Excel Date Countdown Formula: Calculate Days Left in Excel

Formulas & functions · Updated November 18, 2026

If the target date is in cell B2, enter =B2-TODAY() in another cell to show the number of days remaining. A future date gives a positive number, today gives 0, and a past date gives a negative number.

The basic Excel date countdown formula

Excel stores dates as serial numbers, so subtracting one date from another returns the number of days between them. In this countdown, TODAY() supplies the current date and B2 supplies the deadline. For example, if today is November 2, 2026, and B2 contains November 10, 2026, the result is 8 days.

To set it up, put a real Excel date in B2, select a different cell for the result, type the formula, and press Enter. The target cell can be on the same worksheet or another sheet; the important point is that the formula refers to the cell containing the date. Copy the formula down a column to create countdowns for multiple deadlines.

The result is a number, not a date. If Excel displays it as a date, change the result cell’s number format to General or Number. Otherwise, the underlying difference may be correct but appear as an unexpected calendar date.

Prepare the target date and countdown cells

Enter the deadline in B2 as a date Excel recognizes, such as 11/10/2026 or Nov 10, 2026. The exact accepted format can depend on your regional settings. You can check whether Excel recognizes the entry by selecting B2 and choosing Home > Number Format drop-down > Short Date. A date stored as text may not behave like a real date in calculations.

Keep the input and result in separate cells: B2 can hold the deadline, while C2 holds the countdown. Add a heading such as “Days left” above the result column so readers know what the number means. For a list, use one deadline per row and place the formula in the corresponding result cell.

When the target date is in B2 and each row has its own date, enter the formula in C2 and fill it down. Excel adjusts the relative reference from B2 to B3, B4, and so on. If every row should count down to one fixed date stored elsewhere, use an absolute reference such as $B$2 in the formula so it stays fixed when copied.

Handle blank dates and past deadlines

A plain subtraction formula treats an empty date cell like zero, which can produce a large negative result. To leave the countdown blank until a date is entered, with target dates in B2 and results in C2, use =IF(B2="","",B2-TODAY()). The formula checks whether B2 is empty before doing the subtraction.

For a countdown that never displays a negative number, with the deadline in B2, use =MAX(0,B2-TODAY()). This returns zero both on the deadline and after it has passed. That can suit a progress dashboard, but it hides how overdue an item is, so it is not ideal when past-due information matters.

If you want to distinguish upcoming, due-today, and overdue items, keep the numeric countdown and add a separate status column, or use conditional formatting. Keeping the number separate from its label makes it easier to sort and use in other calculations. Decide first whether a negative value is useful: it tells you exactly how many days have elapsed since the deadline.

Count business days instead of calendar days

The basic subtraction counts every calendar day, including Saturdays, Sundays, and holidays. For a workday countdown, suppose the deadline is in B2 and holiday dates are listed in D2:D10. Enter =MAX(0,NETWORKDAYS(TODAY()+1,B2,$D$2:$D$10)) in the result cell to count weekdays after today through the deadline, excluding the listed holidays.

For example, if today is Monday and the deadline is Friday, the formula counts Tuesday through Friday: four workdays. Adding one to TODAY() excludes the current day. The MAX function prevents a negative workday count when the deadline has passed or is today. If you do not have a holiday list, the holiday argument can be omitted: with the deadline in B2, use =MAX(0,NETWORKDAYS(TODAY()+1,B2)).

NETWORKDAYS treats Saturday and Sunday as weekend days. If your organization uses different weekend days, Excel’s NETWORKDAYS.INTL function allows a weekend pattern to be specified. Check that the holiday cells contain actual dates, not text, and that the dates cover the period you want to exclude.

Choose how to count today and handle times

A standard countdown to a date uses the difference between the target date and today, so the due date itself shows 0. Some checklists instead call the due date “day 1” or include today in the count. With the target date in B2, an inclusive calendar-day count is =B2-TODAY()+1. Under that convention, a deadline that is today returns 1, not 0. State which convention you use so readers do not mistake an intentional difference for an error.

If B2 contains a date and a time, subtracting TODAY() can return a decimal because Excel stores times as fractions of a day. For a whole-day countdown that ignores the time portion, with the date and time in B2, use =INT(B2)-TODAY(). For a countdown that measures elapsed time more precisely, with the deadline date and time in B2, use =B2-NOW(); NOW() includes the current time, so the result is a fraction of a day as well as a number of days.

Choose the time-aware version only when the exact time matters, such as a submission cutoff. A date-only deadline normally calls for TODAY(), which avoids a result that changes during the day just because the clock advances.

Format the result and keep it current

Format the countdown result as General or Number, with zero decimal places for whole-day counts. Use Home > Number Format drop-down > Number if you want a fixed number format. A negative number is a valid result for a past deadline; it is not, by itself, evidence that the formula failed.

TODAY() updates when Excel recalculates the workbook. With automatic calculation enabled, it generally refreshes when the workbook opens or when a calculation-triggering change occurs. If a countdown seems stuck, check Formulas > Calculation Options > Automatic. In manual calculation mode, formulas may not update until calculation is triggered.

Because TODAY() is a volatile function, Excel recalculates it as part of workbook recalculation. That is usually convenient for a small deadline list. For a historical record that must preserve the exact date a status was recorded, do not rely on a live TODAY() formula: record a fixed date value instead, since a live countdown is designed to change over time.

Common errors and how to fix them

  • The result looks like a date: Change the result cell’s number format to General or Number. The subtraction may be correct even if the display format is inappropriate.
  • The result is unexpectedly negative: Check whether the deadline has passed, whether the date was entered in the intended month and day order, and whether the workbook uses a different regional date convention.
  • The formula returns an error or does not calculate: Confirm that the target cell contains a real date rather than text. Re-enter the date using a format accepted by your Excel settings, then check that the formula references the right cell.
  • A blank row shows a large negative value: Use the blank-safe IF version described above, or leave the formula out of rows without dates.
  • The countdown does not change: Check that calculation is set to Automatic and that the formula uses TODAY() rather than a date typed directly into the formula.

Also check for hidden time values if a date that appears to be today returns an unexpected decimal or whole-day difference. A cell can display only its date while still storing a time. The INT version removes the time portion when the countdown should be based only on calendar dates.

Highlight deadlines with conditional formatting

A number is useful, but color can make urgent deadlines easier to spot. To highlight dates that are already past, select the deadline range and choose Home > Conditional Formatting > New Rule. Select “Use a formula to determine which cells to format,” enter the rule, choose a format, and confirm. With the first selected deadline in B2, use =B2<TODAY(). If the selected range begins on another row, adjust the row number to match its first cell.

For a countdown column, a separate rule can highlight approaching dates, but choose the threshold that fits your work—for example, a warning when fewer than seven days remain. Keep the formula reference aligned with the first row of the selected range, and use a relative row reference when the rule should evaluate each row independently. Conditional formatting changes appearance; it does not change the countdown value.

Frequently Asked Questions

What is the simplest Excel formula to count down to a date?
With the target date in B2, use =B2-TODAY(). A future date returns the number of calendar days remaining, today returns 0, and a past date returns a negative number.

How do I make the countdown show “Due today” instead of zero?
Keep the date difference as a number and add a status label in a separate cell or column. This keeps the calculation easy to sort and reuse while letting you display words for important states.

Can Excel count only weekdays until a deadline?
Yes. Use NETWORKDAYS with TODAY()+1 as the start date and the deadline as the end date. Supply a range of holiday dates as the optional third argument if those days should not count.

Why is my Excel countdown showing a negative number?
A negative result means the target date is before today. It indicates how many calendar days have passed since that date. If you would rather show zero after a deadline, use a MAX formula to cap the result at zero.

Does the countdown update automatically every day?
TODAY() updates when Excel recalculates the workbook. Automatic calculation is the usual setting; if the value is stale, check Formulas > Calculation Options and select Automatic.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.