## 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.

### 58 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.

18. Stephen says:

Hi I am trying to create a spreadsheet with colum A being date when previous years service carried out and Column B the date in the current year service carried out. Column C showing the variance of days between the A and B. The service should be carried out annually however we try to do it every ten months so that we meet the requirements. I would also like column D to have traffic lights for green for prior to 10 month, Amber for 10 to 11months and red for 12 months and over. Can you give me any advice please. Thank you

19. Kate Choi says:

Thanks for sharing your knowledge ! I’m from south Korea and I am really appreciate your help! You helped me a lot. Have a great day! 🙂

20. Melvin Timi says:

hey Steve, please i need an excel sheet that can days countdown and the will be color variation when it is less than lets say 5 days and when it is above 7 days it should give me the warning color.
please can you send me a mail so we can chat about it.

• Steve Chase says:

Hey Melvin, I am unable to fulfill your request. I’d suggest posting this inquire into Microsoft Office Excel community forums. You should be able to get a response from the Excel team. It is free to post your questions there, you just need a Microsoft email to do so. Google “Microsoft Office community forums”

• Raul lugo says:

You will need to use “conditional formatting” and “format only cell that contain” and you can use less than, equal than, greater than and coloring the cell depending if the condition is accomplished.

21. Khalil Ishag says:

hi Steve and every one am still interesting to know If any one found if there a way to get the seconds to countdown automatically, instead of needing to hit F9 to update?

• Steve Chase says:

Sorry, I don’t have any suggestions.

22. Lisa Satterlund says:

Steve, how do I complete the step quoted below without having the program just string all the numbers together? IOW, where in the formula do I put the labels?

“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”

• Steve Chase says:

Hi Lisa, I used range names in my formulas. =Days& ” days “&Hour& ” hours “&Minute & ” minutes ” & Second& ” seconds” where Days is a range name for the value of cell H1. Range names are not necessary. If you didn’t have range names you would substitute the actual cell range like =H2 & “days…

23. Lisa Satterlund says:

I tried this both per Steve’s original blog post and Francisco’s comment. Interestingly, Francisco’s method runs 59 seconds faster than Steve’s. Any clue as to why? The only difference is in the formatting of the cell containing the target date.

• Steve Chase says:

No clue…. sorry

• Lisa Satterlund says:

No worries. I thought it was interesting, is all.

24. Chris Gordon says:

Hey Steve, you are the man! Quick question, how to I make the hour timer stop at 0 when the Event Date has come and gone (I am only using Days and Hours)? I am using this for a buyer/seller checklist for The Due Diligence period, so every time they open the checklist the countdown for the end of due diligence is displayed. They will continue to use the checklist up until the closing date, but I don’t want hours displayed saying they still have time for due diligence. HELP!

-Chris

25. Mukesh Patel says:

Hi Steve.
This is exactly what I was looking for. Thank you for the really great article.

Thanking you!
Mukesh

26. Oliver says:

Hi Steve.
I have similar problem like others, who asked you about automation of countdown.

Isnt it possible to use Macro and loop the process of Refresh with F9?

I am only the beginner in VBA, so I am not sure how to do that, but I assume that it is possible.

27. Ralph Powell says:

Great explanations thanks Steve. I am trying to create a count-down timer function/s for football events without using VBA or Macros, to record exact time an incident occurs-such as a goal kick
I have been using example =IF(D97″”,IF(E97=””,NOW(),E97),””) which displays the CPU clock time. What I really require is a countdown timer so when a player number is selected (from a data validation dropdown list) in cell D97, excel function/s records the event time in cell E97.
e.g. Player number being 15 – Countdown timer = 18:45 (18 mins and 45 secs since start of the game. I assume some type of start function will be needed which allows me to set the duration of the game and start and stop – probably manually.