Excel Days Until Date: Formulas, Examples, and Fixes

Formulas & functions · Updated November 18, 2026

To calculate calendar days until a date in Excel, subtract today’s date from the target date: with the target date in A2, enter =A2-TODAY(). Format the result cell as General or Number; a positive result means the date is ahead, zero means it is today, and a negative result means it has passed.

The basic days-until formula

Excel stores dates as serial numbers, so subtracting one date from another returns the number of days between them. TODAY() supplies the current date, without a time component, and the target date is the value in A2. For example, if today is November 3, 2026, and A2 contains November 20, 2026, the result is 17 calendar days. This is a date difference, not an inclusive count of every calendar date touched.

Assuming A2 contains the target date and B2 is the result cell, enter this in B2: =A2-TODAY(). If you copy the formula down a list, Excel adjusts A2 to A3, A4, and so on. TODAY() remains a function and updates when the workbook recalculates.

For a single target date, you can type the formula directly into a blank cell. For a schedule, put the target dates in one column, enter the formula in the first adjacent result cell, then use the fill handle or copy and paste it down the column. Give the result column a heading such as Days Remaining so that a negative value is not mistaken for an error.

How TODAY and date subtraction work

TODAY() returns the computer’s current date as recognized by Excel. It does not include the current time. Subtracting it from a valid date in A2 therefore compares two calendar dates and produces a whole-number day difference. Excel accounts for months of different lengths and leap years automatically; there is no need to build a separate formula for February or year changes.

TODAY() is a dynamic function, not a permanent timestamp. Excel normally recalculates formulas when the workbook opens or when workbook data changes. It does not continuously tick like a countdown timer, so a displayed value may not change exactly at midnight unless Excel recalculates. On Windows, check that Formulas > Calculation Options > Automatic is selected if the result seems stale. Mac menu labels and available calculation settings can vary slightly by Excel release.

For a deadline, the usual subtraction formula makes a useful distinction: the deadline itself returns zero, while a date after the deadline returns a negative number. If today is November 20 and the target date is November 20, the result is zero. If the target date is November 19, it is -1. That behavior is often desirable for tracking overdue items, because it shows how many days late they are.

Set up a clear countdown column

  1. Enter real Excel dates in a target-date column, such as A2:A20. You can type a date in a format Excel recognizes for your regional settings, or choose a date from the date-entry workflow your organization uses.
  2. Put a heading such as Days Until in B1. In B2, enter the subtraction formula using A2 as the target.
  3. Format the result cells as General or Number. If Excel displays a date rather than a count, the formula may be fine but the result cell is formatted as a date.
  4. Copy or fill the formula down beside the remaining target dates. Check the first and last rows to confirm each formula points to the date on the same row.

To set a number format in Excel for Microsoft 365 on Windows, select the result cells and use Home > Number Format in the Number group, then choose General or Number. You can also open Format Cells with Ctrl+1 and choose a suitable category. On Mac, Command+1 opens Format Cells. Formatting changes how the result is displayed; it does not change the underlying calculation.

Choose the right kind of day count

Calendar days are the right choice when weekends and holidays count toward the deadline, as they commonly do for a birthday, trip, subscription date, or calendar-based event. The basic formula counts every date boundary between today and the target. If the question is instead how many working days remain, use a workday function; it has different counting rules.

Some readers expect the target date to count as one day. The subtraction formula does not do that: if the target is today, it returns zero. For a simple inclusive calendar count, add one to the difference. With the target date in A2 and today represented by TODAY(), use =A2-TODAY()+1. If today is November 3 and the target is November 20, this returns 18, counting November 3 and November 20 as included dates. Decide which convention fits the task before sharing a count, since “days until” often means elapsed date difference, while a checklist may count both endpoints.

If you want to avoid showing negative values for dates in the past, use a floor of zero. With the target date in A2, enter =MAX(0,A2-TODAY()). For a November 19 target when today is November 20, this displays zero rather than -1. This does not label the date as overdue, however; if overdue status matters, retain the negative number or add a separate status column.

Count working days and holidays

NETWORKDAYS counts weekdays between two dates and treats both the start and end dates as part of the interval when they are working days. For a basic weekday count from today through the target date, with A2 containing the target, use =NETWORKDAYS(TODAY(),A2). If today is a weekday and the target is two weekdays later, the result is 3 because it counts today, the intervening weekday, and the target weekday. This differs from the ordinary days-until formula, which would return 2.

If your meaning is “full working days after today, up to and including the target,” exclude today by starting the range tomorrow. With the target date in A2, use =MAX(0,NETWORKDAYS(TODAY()+1,A2)). For a target on the next weekday, this returns 1. The MAX function keeps the answer at zero when the target is today or already past, rather than presenting a negative working-day count.

To exclude holidays as well as weekends, list holiday dates in D2:D10 and pass that range as the optional third argument. Assuming A2 is the target and D2:D10 contains actual Excel dates for holidays, use =NETWORKDAYS(TODAY(),A2,$D$2:$D$10). The dollar signs keep the holiday range fixed when you copy the formula down. A holiday is excluded if it falls within the counted interval; a date already on a weekend does not create an extra deduction.

For a different weekend pattern, NETWORKDAYS.INTL accepts a weekend code. For a Friday-Saturday weekend, with the target in A2 and holiday dates in D2:D10, use =NETWORKDAYS.INTL(TODAY(),A2,7,$D$2:$D$10). Code 7 represents Friday and Saturday as weekend days. Choose the pattern that matches the relevant work schedule; this function counts workdays according to the selected weekend pattern, not an employer’s full leave policy.

Dates with times and other useful variants

If A2 contains both a date and a time, subtracting TODAY() may return a decimal because Excel stores times as fractions of a day. For example, half a day is stored as 0.5. If the intended question is calendar days until the date, discard the time portion first. Assuming A2 contains the date-time value, use =INT(A2)-TODAY(). INT removes the fractional time from a positive date-time serial, leaving its date portion.

If you actually need a countdown that includes hours and minutes, the target time and current time both matter. TODAY() is not sufficient because it has no time-of-day component; NOW() returns the current date and time. Date-time subtraction produces a fraction of a day, so multiplying by 24 gives hours, but the displayed result and rounding choice should be set deliberately. For deadline decisions, clarify the time zone and whether the deadline is at the start or end of the stated date. A date-only countdown cannot resolve those details.

To display a friendly label such as “days left,” keep the numeric calculation in one cell and place the wording in a neighboring heading or label. Keeping the count numeric makes it easier to sort, filter, chart, or use in conditional formatting. Mixing words into the result can make later calculations less convenient.

Common errors and how to fix them

  • The answer looks like a date. The result cell may have Date formatting. Change it to General or Number; subtraction still works underneath.
  • The result is a large or unexpected number. Check that A2 is a real date value and that the formula refers to the intended cell. Excel may interpret an unfamiliar typed date according to regional settings, so inspect the displayed date and use a consistent format.
  • The formula returns an error or does not calculate. A target entered as text may not behave as a date. Re-enter it in a recognized date format or convert it to a real date value before calculating. Also confirm the formula begins with an equals sign and that calculation is set to Automatic.
  • The number is negative. This normally means the target date is in the past. It is not inherently an error; decide whether to show overdue days, cap the result at zero, or display a separate past-due status.
  • The count changes unexpectedly. TODAY() recalculates, and the result may change as the date changes. If a historical report needs the value frozen, copy the result and use Paste Special > Values, understanding that it will no longer update automatically.

Another easy mistake is comparing a date to text that only looks like a date. Excel dates are numeric values displayed with date formatting, whereas text remains text even if it resembles a calendar date. If a column was imported from another system, test a few entries and check alignment or sort behavior; text dates can sort differently from true dates. Correcting the source data is more reliable than adding increasingly complicated formulas around it.

When to use an alternative

Use direct subtraction for ordinary calendar-day countdowns because it is short, transparent, and naturally returns negative values for past dates. Use NETWORKDAYS when weekends should be excluded, and add a holiday list if the relevant definition of a working day excludes specific dates. Use NETWORKDAYS.INTL when the weekend is not Saturday and Sunday. These functions answer different questions, so selecting among them is a matter of defining the count rather than finding one universally best formula.

A separate status column may be preferable in a project tracker: one field can show a numerical number of days, while another communicates whether an item is upcoming, due today, or late. That makes sorting and reporting clearer than hiding negative values. For a report that must preserve what was true on a past date, use a stored snapshot or fixed value rather than relying on TODAY(), since a live formula recalculates against the current date.

Frequently Asked Questions

What is the Excel formula for days until a date? Put the target date in A2 and enter =A2-TODAY() in another cell. A positive result is the number of calendar days ahead; zero means today; a negative result means the date has passed.

How do I count days including today? With the target date in A2, use =A2-TODAY()+1 for an inclusive calendar count. This counts both today and the target date, so it returns one when the target is today.

How do I calculate working days until a date in Excel? With the target date in A2, use =NETWORKDAYS(TODAY(),A2) to count weekdays inclusively, then provide a holiday range as the third argument if required. Remember that the result includes today when today is a weekday.

Why does Excel show a date instead of the number of days? The result cell is likely formatted as a date. Select it and change its number format to General or Number. The formula result is a day count; the cell format controls its appearance.

Will TODAY update automatically? TODAY() updates when Excel recalculates the workbook, such as when it opens or when changes trigger recalculation. It is not a second-by-second timer, and a result may need recalculation to reflect a new calendar day.

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.