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:
Download my example spreadsheet Date Countdown Timer.
Steve Chase Docs 
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)?
Laura,
You can use the networkdays function to count the number of days excluding weekends. There is also a third optional arguments to exclude holidays.
https://support.office.com/en-us/article/NETWORKDAYS-function-48e717bf-a7a3-495f-969e-5005e3eb18e7?CorrelationId=35781da6-d3c9-489d-b328-6bc21ff85547&ui=en-US&rs=en-US&ad=US
Steve
is there a way to get a notification when when the clock strikes 0?
Michael, I don’t know a way to get a notification when the clocks strikes 0.
Steve
Im so confused. I am trying to do a countdown formula for my job spreadsheet.
Sabrina, What is your countdown looking to accomplish? How many days before a projects starts? Or do you need to make It more detailed?
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.
Dev, thank you for pointing this out for us. My apologies.
Good to know.
Steve
Has this been fixed in the template provided?
Here is the correction file. https://1drv.ms/x/s!AmOflzuTYrYwlZBVLZKHH7j4XM2K3w
Thanks for the assistance. I can now plan my work schedule effectively.
Thanks George
Is there a way to have it automatically update once a minute or so?
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
A1 = Target Temp B1 = 375
A2 = Current Temp B2 = ?
Place the seconds down the A column. I have an example spreadsheet you can download from my OneDrive folder here. https://onedrive.live.com/redir?resid=30B662933B979F63!260485&authkey=!AGMxPTUb6dg7Bvg&ithint=file%2cxlsx
The spreadsheet has a custom format for the formulas to include the word “seconds” after each value.
Steve
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.
thank you
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.
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.
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.
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.
I’m sorry that I cannot assist you right now.
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
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. 🙂
Steve Chase this is still not working, I used this with next day event from now, but it is displaying -364 days.
You should correct this by adding +365
=INT(42735,7916666667-NOW()+365) gives the days to New Years dinner
Thanks
Farooq Iqbal
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. 🙂
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
Hi Tracey,
I’m sorry for the long delay. I am not certain how to do this without using VBA programming in Excel or Access.
Now we can have the date conditionally formatted based on 3 months. The formula is using the DATEDIF. Here is a video on how this works.
=DATEDIF(TODAY(),A2,”m”)=3
https://onedrive.live.com/redir?page=view&resid=30B662933B979F63!383083&authkey=!AGZokq80_iqzrGU is a workbook example
Steve
you can put expiry date say in Cell A1, and in B1 you can use the formula = A1-Today()
Then use Graded conditional formatting based on remaining days, say if more than 30 day remaining show GREEN, , less than 2 weeks remaining show YELLOW, less than a week remaining show RED signal
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
Thank’s in advance
Try this out. https://1drv.ms/x/s!AmOflzuTYrYwmYJZrS7-BbuQ6iDn5A
https://1drv.ms/x/s!AmOflzuTYrYwmYJZrS7-BbuQ6iDn5A
this should work
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
I think you might want to pursue the NETWORKDAY() function. For the 3 days a week you might need to list all the dates in a column that are dates you coworker is not working M-F. Weekends are excluded by default. https://support.office.com/en-us/article/NETWORKDAYS-function-48E717BF-A7A3-495F-969E-5005E3EB18E7
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?
not that I am aware of
Any way of doing a paste link into Word?
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.
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
Hey Stephen, Maybe this post might get you going in the right direction. https://stevechasedocs.wordpress.com/2014/10/02/how-to-use-excel-traffic-lights-with-conditional-formatting-dates/ If you want to get my one on one attention please contact me at http://www.sequentiasolutions.com and we could work together to accomplish this task.
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! 🙂
I’m glad it was helpful
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.
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”
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.
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?
Sorry, I don’t have any suggestions.
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”
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…
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.
No clue…. sorry
No worries. I thought it was interesting, is all.
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!
Thanks in advance
-Chris
Hi Chris, Thanks for the compliment. I am not able to fulfill your request. If you would like to send an inquire to the MS Excel community forums they would be a great free resource to ask. https://answers.microsoft.com/en-us/search/search?SearchTerm=excel&IsSuggestedTerm=false&tab=&isFilterExpanded=false&CurrentScope.ForumName=&CurrentScope.Filter=&ContentTypeScope=#////1
Hi Steve.
This is exactly what I was looking for. Thank you for the really great article.
Thanking you!
Mukesh
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.
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.
Hi Steve
This was fantastic!!! Thank you for creating and including also the sheet.
I wanted to arrange all my college assignments by due date with an exact day countdown function and what you covered here helped me to do just that.
Thanks again,
Rita
Glad you like it Rita!
Hi, this does not work when Event minute or second is less that Now() minute and second. Also this countdown time is not going to work if
Current date and time is 27/10/2019 10:45:00 am
Even date and time is 28/10/2019 10:40:00 am
It is less than 24 hours by 5 minutes.
Hi,
Just need a help
How to create a automatic countdown for a specific date and time .i have searched in you tube also i could not able to find that so kindly help me with this task
Thanks and regards ,
Adhirudhya S