To calculate days until a due date in Excel, put the due date in A2 and enter =A2-TODAY() in B2. The result is the number of calendar days remaining; a negative result means the date has passed, and zero means it is due today.
How the days-until-due formula works
Excel stores real dates as serial numbers, so subtracting one date from another gives the number of days between them. TODAY() returns the current date, while A2 represents the deadline. The formula therefore subtracts today’s date from the due date. Format the result cell as General or Number so it displays as a count, not as a calendar date.
For example, if TODAY() returns November 2, 2026, and A2 contains November 10, 2026, =A2-TODAY() returns 8. If A2 contains October 30, 2026, it returns -3, meaning the deadline was three calendar days ago. These examples use the dates stated; results change with the date on which Excel recalculates the formula.
This calculation counts the difference between dates, not both endpoints inclusively. A deadline eight days after today returns 8, rather than 9. That convention is useful for tracking how long remains before a deadline. If your process defines the due date itself as an additional countable day, make that convention explicit before changing the calculation.
Set up a due-date tracker
A simple worksheet can keep deadlines, remaining days, and status together. For example, put task names in column A, due dates in column B, days remaining in column C, and status in column D. Enter the formula in C2 and copy it down for the other rows.
- Enter a task name in A2 and a due date in B2. Make sure B2 contains an actual Excel date, not text that merely looks like one.
- In C2, enter
=B2-TODAY(). Set C2 to General or Number if Excel displays a date instead of a number. - Copy the formula down by dragging the fill handle at the lower-right corner of C2, or copy and paste it into the remaining rows. Relative references adjust automatically: the next row will refer to B3.
- Optionally add a status formula or conditional formatting, described below, to make urgent and overdue items easier to spot.
Keeping the due date in a separate column is usually clearer than typing dates directly into formulas. It makes records easier to edit, sort, filter, and audit. If you maintain a shared tracker, use a consistent date format such as 2-Nov-2026 or 2026-11-02 so that readers are less likely to confuse day and month.
Show overdue, due-today, and upcoming status
A negative number is mathematically correct, but a text label may be easier to scan. With due dates in B2:B20, enter this in D2 and copy it down:
=IF(B2="","",IF(B2<TODAY(),"Overdue by "&(TODAY()-B2)&" days",IF(B2=TODAY(),"Due today",(B2-TODAY())&" days left")))
This leaves the status blank when there is no due date, reports “Due today” for today’s date, and otherwise reports either overdue days or days left. For instance, if today is November 2, 2026, a due date of October 30 produces “Overdue by 3 days,” while November 10 produces “8 days left.” The number in parentheses ensures the subtraction is evaluated before it is joined to the text.
Because this formula returns text, do not use its results for numerical calculations or numeric sorting. Keep the numeric days-remaining formula in another column if you need to sort from most urgent to least urgent, calculate averages, or build a chart. You can sort the numeric column smallest to largest to put overdue items first.
Keep blank rows and date values under control
A blank due-date cell can behave like zero in date arithmetic, producing a large negative number. Add a blank check when the sheet includes incomplete records. With due dates in B2:B20, use =IF(B2="","",B2-TODAY()) in C2 and fill down. It returns a blank until a due date is entered, then calculates the day difference.
If the result looks like a date such as January 8, 1900, the subtraction may be correct but the result cell is formatted as a date. Select the result cells and choose Home > Number Format, then choose General or Number. If you see a number such as 46000 where you expected a date in the input cell, that number may be Excel’s stored date serial; apply a date format to the input instead.
A date that looks correct but gives an unexpected answer may actually be text. One check is to change the cell’s format to General: a genuine date typically displays as a serial number, whereas text remains text. Re-enter the date, use a consistent regional date format, or convert imported text to dates before relying on the calculation. Avoid guessing whether 03/04/2026 means March 4 or April 3; that depends on the workbook’s regional settings and how the value was imported.
For a deadline that includes a time, subtracting TODAY() can return a decimal fraction of a day because Excel stores time as part of the date value. If you only care about the calendar date, use INT on the deadline. With a due date and time in B2, the calendar-day formula is =INT(B2)-TODAY(). If hours and minutes matter, choose a time-aware calculation and format the result appropriately instead of discarding the time.
Count working days instead of calendar days
Some deadlines are measured in weekdays rather than calendar days. Excel’s NETWORKDAYS function counts Monday through Friday between two dates and can exclude listed holidays. With the due date in B2 and holiday dates in F2:F10, use =NETWORKDAYS(TODAY(),B2,$F$2:$F$10)-1 for an upcoming deadline. The subtraction makes today count as zero days remaining: if today is Monday and the due date is Tuesday, the result is 1. Without the holiday range, use =NETWORKDAYS(TODAY(),B2)-1.
Enter real Excel dates in the holiday range, and keep the reference absolute as shown so it stays fixed when you copy the formula down. This calculation treats Saturday and Sunday as nonworking days and does not automatically know about company closures or local holidays; the holiday list supplies those exclusions. NETWORKDAYS is designed for dates in chronological order, so use a separate approach or handle past deadlines explicitly if the tracker also needs meaningful negative working-day counts.
For a schedule with a different weekend pattern, Excel provides NETWORKDAYS.INTL, which lets you specify which days are weekends. Check its weekend-code options and test them against your organization’s workweek before using the result. For many trackers, calendar days are simpler and less ambiguous; use working days only when the deadline policy actually defines time that way.
Highlight deadlines with conditional formatting
Color can make a long list easier to review without replacing the underlying day counts. To highlight overdue due dates in B2:B20, select that range and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =AND(B2<>"",B2<TODAY()), choose a format, and confirm. The rule checks that the cell is not blank and its date is earlier than today.
To highlight deadlines from today through the next seven days, create another rule for the same range using =AND(B2>=TODAY(),B2<=TODAY()+7). The seven-day window includes today and the date seven days from today. If you apply a formula-based rule to a different range, make sure the cell reference corresponds to the first cell in the selected range. A misplaced row reference can make the colors appear shifted.
Conditional formatting changes appearance, not the stored value. You can still sort, filter, and calculate using the dates. On Excel for Mac, the Conditional Formatting controls are also on the Home tab, although the dialog layout can vary by release. Excel for the web offers conditional formatting as well, with some interface differences.
Understand when TODAY updates
TODAY() is a volatile date function: its displayed result can change when Excel recalculates the workbook. It does not act as a continuously ticking countdown that refreshes every second. In the usual Automatic calculation mode, it updates as the workbook recalculates, including when it is opened or when relevant edits trigger calculation. To check the workbook setting in Microsoft 365 for Windows, use Formulas > Calculation Options > Automatic.
If a tracker still shows yesterday’s count, check whether calculation is set to Manual. You can recalculate with F9 on Windows, or restore Automatic using the menu path above. Mac keyboard behavior can depend on the keyboard’s function-key settings, so the ribbon command is a reliable alternative. If a date is meant to be a fixed record of when something happened, do not use TODAY() for that record: use a fixed date value, since TODAY() is intended to keep changing.
Common choices and limitations
Choose the calculation that matches the question the tracker is meant to answer. Calendar-day subtraction is the simplest and clearest option for most due-date lists. NETWORKDAYS is appropriate when weekends and specified holidays do not count. A status formula is useful for labels, while conditional formatting is useful for visual scanning. These options can be combined, but keeping a numeric result alongside any text status preserves the ability to sort and analyze the values.
- Use
=B2-TODAY()for a signed calendar-day difference: positive is upcoming, zero is due today, and negative is overdue. - Use
=IF(B2="","",B2-TODAY())when blank rows should stay blank. - Use
=NETWORKDAYS(TODAY(),B2)-1for future weekday counts when weekends are Saturday and Sunday. - Use a separate status or formatting rule when people need a quick visual signal rather than only a number.
One common misconception is that a negative result means the formula has failed. It is often the most useful signal that an item is overdue. Another is that a due date automatically excludes nonworking days. It does not: ordinary subtraction counts every calendar day, including weekends and holidays. The formula cannot determine a company’s grace period, cutoff time, or contractual definition of “due”; those rules need to be represented separately and checked against the relevant policy.
Frequently Asked Questions
How do I calculate days until a due date in Excel?
Put the due date in A2 and enter =A2-TODAY() in another cell. Format the result as General or Number. Positive values are days remaining, zero means due today, and negative values show how many days overdue.
How do I stop Excel showing a negative number for overdue tasks?
With the due date in A2, use =MAX(0,A2-TODAY()) to display zero rather than a negative count after the deadline. This hides how overdue an item is, so keep a separate status or signed calculation if that information matters.
How do I leave the result blank until I enter a due date?
With the due date in A2, enter =IF(A2="","",A2-TODAY()). The formula returns an empty display for a blank input and calculates the day difference after a date is entered.
Can Excel count only business days until a deadline?
Yes. With an upcoming due date in A2, =NETWORKDAYS(TODAY(),A2)-1 counts weekdays remaining while treating today as zero. Add a holiday-date range as NETWORKDAYS’ third argument when those dates should not count.
Why does my days-until-due result show as a date?
The answer cell is probably formatted as a date. Select it and choose Home > Number Format > General or Number. Also confirm that the due-date input is a real Excel date rather than text.
Choose a formula that matches the deadline
For an ordinary calendar deadline, start with date subtraction and decide whether overdue values should remain negative or be replaced by a label. Add a blank check for incomplete rows, use NETWORKDAYS only when business-day counting is required, and use conditional formatting to make priorities visible. Once the date cells contain genuine Excel dates and the result cells use a numeric format, the calculation is simple, updates with TODAY(), and can be copied through a practical due-date tracker.
Related
- Excel Power Query: Combine Columns Without Losing Data
- Excel Not Summing Numbers? Causes and Fixes
- Excel Not Showing All Rows? Find and Restore Hidden Rows
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Add Double Quotes in an Excel Formula for TextTo show double quotation marks in text produced by an Excel formula, type each quote mark twice inside…
- Excel Formula for Weeks Between Two Dates: Full WeeksTo calculate weeks between two dates in Excel, subtract the start date from the end date and divide…
- Power Query Lookup: How to Match Data Across TablesIn Excel, a Power Query lookup is usually a merge: open the Power Query Editor, choose Home >…
- Find and Replace Formula in Excel: SUBSTITUTE or REPLACEIn Excel, use SUBSTITUTE to replace matching text inside a cell, or REPLACE to replace characters at a…
- Excel Formula for the Tab Name: Get the Worksheet NameYes. In Excel for Microsoft 365 on Windows, enter =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) in a cell on the worksheet whose name…
- Excel MONTH Function Examples: Formulas and TipsExcel’s MONTH function extracts the month number from a date, returning an integer from 1 (January) to 12…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.