Example 1 – Use the TODAY Function to Create a Day Countdown in Excel
Here is a common template of the TODAY formula. We’re going to make a countdown for the Summer Olympics 2024 starting on 26th July.
Steps:
- In cell C3, put the starting date of the Summer Olympics 2024.
- In cell B4, put the following formula.
=C3-TODAY()
- Press Enter.
- From the Home tab, go to the Number Format dropdown and choose the General format.
- The Date format is changed to the General format.
- We changed the starting date to the Long Date format to make it more reader-friendly.
Read More: Excel Formula to Calculate Number of Days Between Today and Another Date
Example 2 – Create a Day Countdown in Excel Using the NOW Function
- In cell B4, input the following formula and press Enter.
=ROUNDUP(C3-NOW(),0)
The ROUNDUP function rounds up a fractional number to the next integer. It takes two arguments-=ROUNDUP(number, num_digits)
We put C3-NOW() as the number argument of the ROUNDUP function. We used 0 as num_digits as we don’t want any fraction number of days but rather a rounded-up number in the display.
If we normally used the function without the ROUNDUP function, the output would look like this.
After converting the format of the output cell to the General format of the output, it would return a fraction of the number of days left to start the event.
Read More: How to Calculate Remaining Days in Excel
Notes
If the event passes, the countdown will start displaying a negative number. For example, we can see a countdown for Copa America 2021 that ended 266 days before the date of writing this article.
- To avoid this and show 0 instead of the negative number of days, we need to use the MAX function.
=MAX(0,C3-TODAY())
Download the Practice Workbook
Related Articles
- How to Calculate Overdue Days in Excel
- How to Calculate Working Capital Days in Excel
- How to Calculate Days Outstanding in Excel
- How to Calculate Average Tenure of Employees in Excel
- How to Calculate Tenure in Years and Months in Excel
- How to Calculate 90 Days from a Specific Date in Excel
<< Go Back to Days Between Dates | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
How can you change the fill/text color as you get closer to the date? Say, 30 days out turn to orange, 7 days out to red, day of/beyond black? Thank you!
Hi, BRENT!
You have asked a fantastic question.
You can change the fill/text color automatically by using conditional formatting. You can apply multiple rules to a cell using this. To learn about conditional formatting in this regard, you can go to the following link.
https://www.exceldemy.com/excel-conditional-formatting-greater-than-another-cell/
Regards,
Tanjim Reza