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.

### Like this:

Like Loading...

*Related*

## About Steve Chase

I'm a proud husband and dad to 4 boys! Microsoft Certified Trainer and Boy Scout Leader are some of things I call myself. The Cincinnati Reds are my favorite team! When not outdoors, I enjoy working with documents in Microsoft Office, QuickBooks, Visio and Photoshop. My wife, Erin, shares her awesome recipes on her blog at 5dollardinners.com.

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

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

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.

https://stevechasedocs.wordpress.com/2016/02/15/using-the-datedif-function-in-excel-for-conditonal-formatting-expired-training-dates-video/

=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