Days Until a Deadline in Excel (Countdowns)

To count the days until a date in Excel, subtract today's date from it: =A2-TODAY(). Dates are numbers, so the difference is the number of days left, and TODAY() makes it update on its own every day. This guide is for anyone tracking task deadlines, contract expiries, renewals or exam dates, and it covers overdue labels, working days, and how to highlight what's due soon.

How to count the days until a date#

Subtract today from the deadline. The later date goes first.

=A2-TODAY()

Excel stores each date as a count of days, so subtracting two dates gives the days between them. TODAY() returns today's date and recalculates whenever the workbook opens, so the countdown drops by one each day. DAYS does the same job with the end date first: =DAYS(A2,TODAY()).

If the cell shows a date like 1/11/1900 instead of 11, Excel has given it a date format. Select it and choose General from the Number Format box on the Home tab (on Windows, Ctrl+Shift+~ also applies General).

Build a deadline tracker step by step#

Use one "as of" cell for today and point every formula at it. That keeps the sheet fast and lets you test any date:

1. In F1, type =TODAY(). (This example uses a fixed date, November 3, 2025, so the numbers stay the same.)
2. In C2, type =B2-$F$1. The dollar signs lock F1, so it doesn't shift when you fill down.
3. Fill down with Ctrl+D (Windows) or ⌘D (Mac).

Zero means the deadline is today, and a negative number means it has passed: the policy update is 6 days late.

How to show Overdue, Due today or days left#

Wrap the countdown in IF to turn numbers into plain-English status labels:

=IF(B2<$F$1,"Overdue by "&$F$1-B2&" days",IF(B2=$F$1,"Due today",B2-$F$1&" days left"))

The first test catches past deadlines, the second catches today, and everything else is in the future. The order matters: IF stops at the first test that is TRUE, so check for overdue before you check for due today. Our IF function guide explains how nested tests run. If you only need a number that never goes below zero, use =MAX(0,B2-$F$1).

How to count working days until a deadline#

Use NETWORKDAYS to skip weekends:

=NETWORKDAYS($F$1,B2)

From Monday, November 3 to Friday, November 14, 2025, this returns 10: two full working weeks. NETWORKDAYS counts both the start and end dates, so today is included. If today shouldn't count, subtract 1.

Add your holiday list as the third argument to skip those days too: =NETWORKDAYS($F$1,B2,$H$2:$H$8). When the deadline has passed, NETWORKDAYS returns a negative number. Our guide to NETWORKDAYS and WORKDAY covers custom weekends and finding a deadline from a number of working days.

How to show months and days left#

For long countdowns, "2 months, 5 days" reads better than "66 days". DATEDIF counts whole months, and EDATE finds the date those months land on:

=DATEDIF($F$1,B2,"M")&" months, "&B2-EDATE($F$1,DATEDIF($F$1,B2,"M"))&" days"

DATEDIF returns #NUM! when the start date is after the end date, so use this only for future deadlines, or wrap it in an IF that checks B2>=$F$1 first. The DATEDIF function guide explains its units. To measure the gap between any two dates rather than counting down, see how to count days between dates.

Countdowns to month-end, year-end and quarter-end#

Combine TODAY() with the date you're counting to:

EOMONTH returns the last day of a month; our guide to the first and last day of the month shows more month-boundary formulas. To set a deadline in the first place, such as 30 days or 3 months after a start date, see adding days, months or years to a date.

How to highlight deadlines that are due soon#

Use conditional formatting so overdue and urgent rows stand out:

1. Select A2:C5 (the rows of the tracker).
2. Choose Home > Conditional Formatting > New Rule.
3. Pick Use a formula to determine which cells to format.
4. Enter =$C2<0 and set a red fill. Click OK.
5. Add a second rule with =AND($C2>=0,$C2<=7) and an orange fill for deadlines in the next week.

The $ before C keeps every cell in the row looking at the Days left column. Overdue tasks turn red and tasks due within 7 days turn orange, and both update as TODAY() moves on.

Deadline trackers like this come up often in admin, project and HR roles. Our guide to Excel skills for HR jobs lists related date tasks, such as probation end dates and contract renewals.

Common countdown mistakes#

These are the problems you're most likely to see:

• The result looks like a date. Change the format to General.
• The dates are text. Subtraction returns #VALUE!. Re-enter the dates or convert them with DATEVALUE.
• Times hidden in the dates. NOW() includes the time, so =A2-NOW() returns decimals. Use TODAY() for whole days.
• TODAY() in hundreds of cells. It works, but one "as of" cell is easier to test and to freeze for a report.
• Counting today twice. NETWORKDAYS includes both ends; plain subtraction doesn't. Pick one rule and use it consistently.

Practise it#

Try the countdown exercise on this page. Then practise date subtraction, DAYS, DATEDIF and TODAY() in the days between dates lesson, and count working days with holidays in the NETWORKDAYS and WORKDAY lesson.