This post will share how to create a date countdown timer in Excel. The final result will result in putting together multiple formulas to show how many days, hours, minutes, and seconds there are leading up to a future event.
First I type some labels in A1 through A4
A1 = “Today’s Date and Time”
A2 = “Hour”
A3 = “Minute”
I enter formulas in column B.
The NOW function returns the current date and time. Pressing the F9 button key will update the value.
The HOUR function returns the current hour expressed in military time. Zero is 12:00AM and 23 is 11:PM.
The MINUTE and SECOND formulas return a value between 0 and 59.
I create a custom format on the NOW function result in B1 to have AM/PM displayed.
Then I enter some labels in D1 through D4 and values in cells E1 through E4
Event Date – 12/20/2016 8:00 PM
Event Hour – 20
Event Minute- 59.9999
Event Second – 59.9999
More labels in cells G1 through G4 and formulas in cells H1 through H4.
I create an IF function that will determine if the event hour is greater than or equal to the current hour. If true, then event hour – current hour. If false, then =24-current hour+event hour. Times are in military 0 through 23.
The INT function rounds down the number to the nearest integer.
I create range names in my individual values from H1 through H4.
Now I can see how many days, hours, minutes, and seconds are left from now till the event date on 12/20/2016 at 8:0o PM. This is all with subtraction formulas in cells H1 through H4.
I finish up with creating a formula that concatenates the values into a string text output.
The & Ampersand is used to join text strings. I use the Range names in the formula in between the labels.
Download my example spreadsheet Date Countdown Timer.