## Create a Countdown Timer With Dates in Excel

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”

A4= “Second”

I enter formulas in column B.

B1 =NOW()

B2=HOUR(B1)

B3=MINUTE(B1)

B4=SECOND(B1)

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.

Days =INT(E1-B1)

Hour =IF(E2>=B2,E2-B2,24-B2+E2)

Minute =INT(E3-B3)

Second =INT(E4-B4)

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.

Final result:

This entry was posted in Excel and tagged . Bookmark the permalink.

### 39 Responses to Create a Countdown Timer With Dates in Excel

1. Laura says:

Hi Steve, This countdown timer is great! Is it possible to, or can you help me enter logic that would remove weekends (isolating work weeks)?

2. Michael Wang says:

is there a way to get a notification when when the clock strikes 0?

• Steve Chase says:

Michael, I don’t know a way to get a notification when the clocks strikes 0.
Steve

3. sabrina says:

Im so confused. I am trying to do a countdown formula for my job spreadsheet.

• Steve Chase says:

Sabrina, What is your countdown looking to accomplish? How many days before a projects starts? Or do you need to make It more detailed?

4. Dev says:

One issue I found is that if Event is 25 hours ahead , for example , current date and time is 11 May 4:15 pm , and event time is 12 May 5:pm , the correct time left is 1 day, 0 hrs, 45 mins. Your formulas return 1 day , 1 hour and 45 mins.

5. George says:

Thanks for the assistance. I can now plan my work schedule effectively.

6. Paul says:

Is there a way to have it automatically update once a minute or so?

7. coffeesnobmarkcoffeesnobmark says:

Hi, I am trying to create a spread sheet for my coffee roasting. What I an looking to do is:
If I am 75 degrees from my target temperature (375) and am increasing my temperature 1 degree every 3 seconds, how long before I reach my target temperature. I’d like to develop this spreadsheet for 1 degree every 3 seconds, 1 degree every 4 seconds, and 1 degree every 5 seconds (If someone can help me with the 1 degree every 3 seconds I think I can take it from there). Thank you very much. I have been trying to figure this out but I am a basic Excel person 🙂
Mark

8. Ana says:

The one issue I found is that, IF the formula for hours is FALSE, then we use 24-B1+E1, but if it is false, then 1 day is not 1 day anymore, it should be O days. For example: IF current event is 25.10.2015. in 23:00 h, and future event equals 26.10.2015. in 17:00 h, we have 18 hours, BUT just 18 hours, and we should exclude that one whole day left defined by formula for days.

9. Emma says:

Hey Steve – I’m trying to make a simple countdown counter from 12:00 noon to 17:00 when the event starts. I need a simple countdown formula in one column for each activity in the lead-up to the event at 5.00pm. Also my horizontal axis has numbers not letters?? I’m using Excel 2011.

• Steve Chase says:

Hi Emma,
Enter =Now() in cell A1. Enter today’s date in cell B1 with the date and time. Example 12/16/2015 5:00 PM.

Subtract the two cells.

=B1-A1.

Format the cell with the answer as h:mm. Within the format cells dialog box, click custom. Change it from general to custom. Enter h:mm.

Press F9 to update the calculation.

Note that Excel cannot display negative values for the time. So once the time is over, then you will get an error.

10. Andrew says:

is there any way to creat a function where i get i can get a 5 day countdown including working days to each cell would tell me how many days i have left for a said lets just day 5 day countdown.

• Alfredo says:

I’m trying to do the same. I need to write just “5” or “10” because I give the task with different days left depending on the scenario.

• Steve Chase says:

I’m sorry that I cannot assist you right now.

11. Francisco says:

I think there’s a more elegant way.

Let say that your New Years dinner 2016 is set to be at 31/12/16 at 19:00 h.

Then the date in Excel dd-mm-yyy HH:MM Format is 31-12-2016 19:00
and in General Format is 42735,7916666667.
In that number, the integer part (42735) contains the date information and the decimal part (0,7916666667) contains the time information, which can be extracted with the =HOUR(), =MINUTE() and SECOND() formulas.

So with that, plus the =NOW() and =INT() formulas you can writ down in the cells.

=INT(42735,7916666667-NOW()) gives the days to New Years dinner
=HOUR((42735,7916666667-NOW())-INT(42735,7916666667-NOW())) gives the hours left
=MINUTE((42735,7916666667-NOW())-INT(42735,7916666667-NOW())) gives the minutes left
=SECOND((42735,7916666667-NOW())-INT(42735,7916666667-NOW())) gives the seconds left

Of course you can define a cell for the “42735,7916666667-NOW()” string, but this leaves it nicely self-contained.

Don’t forget to set the format of each cell to General, or you will get some date back in 1900.

I hope this helps!

Francisco

• Steve Chase says:

Francisco, This is brilliant. Thanks for sharing your knowledge with this community. I created a template using your formula and it worked perfectly. I want to make it available to anyone who wants the template to download from my training files. Here is the link. https://onedrive.live.com/redir?resid=30B662933B979F63!346197&authkey=!AC2Shx-4-FzNit8&ithint=file%2cxlsx

Thank you Francisco!

Steve
P.S. I do look forward to my New Year’s dinner 12/31/2016 at 7PM with my family. 🙂

• Farooq Iqbal says:

Steve Chase this is still not working, I used this with next day event from now, but it is displaying -364 days.

• Farooq Iqbal says:

You should correct this by adding +365
=INT(42735,7916666667-NOW()+365) gives the days to New Years dinner
Thanks
Farooq Iqbal

12. Steve Chase says:

Francisco, This is brilliant. Thanks for sharing your knowledge with this community. I created a template using your formula and it worked perfectly. I want to make it available to anyone who wants the template to download from my training files. Here is the link. https://onedrive.live.com/redir?resid=30B662933B979F63!346197&authkey=!AC2Shx-4-FzNit8&ithint=file%2cxlsx

Thank you Francisco!

Steve

P.S. I do look forward to my New Year’s dinner 12/31/2016 at 7PM with my family. 🙂

13. Tracey says:

Hello Steve, I am trying to add a formula to an excel spreadsheet which would notify me when Drivers licences and other plant and machinery tickets are due for renewal. Ideally I would love to add the date of attainment and then have the formula calculate how many months till expiry, possibility with a red warning box once it gets to three months? Is this possible? Cheers Tracey

14. Rabah says:

Hello steve
I need a progress bar in excel, I mean colored bar progress, to show time passed between starting date mentioned in an excel cell and the finish date mentioned in an other excel cell, the date progress bar will be just colored to let me know for example if i am in the middle of the time allocated to the project just by seeing the half of the cell colored or somthing like that

15. Paul says:

Hi Steve, I have created a countdown clock for a colleague who is counting down to retirement, everything worked fine, but now another colleague has asked me to do a countdown clock for them but they only work 3 days a week, can you advise how to only count 3 days a week as working days?
Thank you

16. Dan says:

Thank you Steve,

I’ve downloaded and changed the numbers into my own excel spreadsheet. Is there a way to get the seconds to countdown automatically, instead of needing to hit F9 to update?

17. Ron says:

Any way of doing a paste link into Word?

• Steve Chase says:

Yes, You can copy a cell from Excel with a link. Copy and paste like normal then after you paste click the little clipboard option and select either link and merge formatting or link and keep source formatting.