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()




now function

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.

Hour function

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.

am pm excel custom format

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)

event date

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.

if statement hours measured

The INT function rounds down the number to the nearest integer.

I create range names in my individual values from H1 through H4.

range names

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:

756 days 23 hours 31 minutes 33 seconds left

Download my example spreadsheet  Date Countdown Timer.



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
This entry was posted in Excel and tagged . Bookmark the permalink.

31 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?

  3. sabrina says:

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

  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 🙂

  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.


      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.

  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!


  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.!346197&authkey=!AC2Shx-4-FzNit8&ithint=file%2cxlsx

    Thank you Francisco!


    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
    Thank’s in advance

  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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s