To calculate calendar days until a date in Excel, put the target date in A2 and enter =A2-TODAY() in B2. The result is the number of days from today to that date; it updates when Excel recalculates the workbook.
The basic Excel days-until formula
For a simple countdown, the only requirement is that A2 contains a real Excel date, not text that merely looks like one. With the target date in A2 and the countdown in B2, use =A2-TODAY(). If today is 2 November 2026 and A2 contains 15 January 2027, the result is 74. The calculation counts the date difference: it does not count today as a day remaining.
Replace A2 with the cell that contains your deadline or event date. For example, if the date is in C5, enter =C5-TODAY() in the cell where you want the answer. You can then fill the formula down a column to calculate a countdown for each date in a list. Excel adjusts a relative reference such as A2 as you copy the formula, changing it to A3, A4, and so on.
A result of zero means the target date is today. A positive number means it is in the future, and a negative number means it has passed. Negative results are useful when you want to see how many days late an item is, rather than hide the fact that its deadline has gone by.
Why subtracting TODAY works
Excel stores dates as serial numbers, so subtracting one date from another gives the number of days between them. TODAY() supplies the current date without a time. The formula therefore subtracts today’s date from the date in A2. It is not a special countdown function; it is ordinary date arithmetic using Excel’s date values.
TODAY() updates when the workbook recalculates, such as when you open the file or make a change while calculation is set to Automatic. It does not act as a continuously running clock that refreshes every second. If a workbook appears to show yesterday’s answer, check that calculation is enabled and recalculate the workbook. On Windows, F9 recalculates open workbooks; Excel for Mac has its own recalculation shortcuts, which can vary with keyboard settings.
Because TODAY() changes over time, this formula is suited to a live countdown, not to recording what the countdown was on a particular day. If you need a permanent snapshot, copy the result and use Paste Special to paste its value, or enter a date manually. A typed date is fixed; a formula containing TODAY() is dynamic.
Enter dates and format the result
Type the deadline into A2 using a date format Excel recognizes, or select the cell and choose a date format from Home > Number. The precise way Excel interprets an entry such as 03/04/2027 can depend on regional settings: it might mean 3 April or March 4. A month name, such as 4 Mar 2027, can make an example clearer, but verify the displayed date before relying on a deadline.
After entering the formula, format B2 as a number with zero decimal places. Select the result cell, then use Home > Number and choose Number; set decimal places to zero if needed. If Excel displays the result as a calendar date, the calculation may still be right: the cell is simply using a date format to display a numeric day difference. Changing the format to Number or General usually makes the count visible.
For a reusable countdown list, a simple layout is helpful:
- Column A: event, task, or deadline date.
- Column B: days remaining formula.
- Optional columns: task name, owner, status, or notes.
Keep the date values in one column and formulas in another. This makes it easier to sort by deadline, filter a task list, or copy the formula down without overwriting the dates. If you format the data as an Excel table, formulas entered in a table column can fill down automatically for new rows.
Count business days instead of calendar days
The basic subtraction counts every day, including Saturdays, Sundays, and holidays. For a work schedule, Excel’s NETWORKDAYS function counts weekdays between two dates and can exclude a supplied holiday list. Its normal weekend is Saturday and Sunday. It includes the start and end dates if those dates are working days, so a countdown usually needs to start tomorrow if the goal is to count days remaining after today.
With the target date in A2 and the business-day result in B2, use =NETWORKDAYS(TODAY()+1,A2). For example, if today is Monday, 2 November 2026, and A2 is Friday, 6 November 2026, this returns 4: Tuesday, Wednesday, Thursday, and Friday. It excludes Monday, because the formula starts on Tuesday, and weekends would not be counted.
To exclude holidays too, enter holiday dates in D2:D10 and use =NETWORKDAYS(TODAY()+1,A2,$D$2:$D$10) in B2. The dollar signs keep the holiday range fixed if you copy the formula down. Use actual Excel dates in that range, and include only the holidays relevant to your organization or location. A holiday falling on a weekend does not reduce the count further because the weekend day was already excluded.
For a deadline today or in the past, the simple NETWORKDAYS version can return a surprising value because its start date is after its end date. If you want zero for those cases, with the same layout of target dates in A2 and results in B2, use =IF(A2<=TODAY(),0,NETWORKDAYS(TODAY()+1,A2,$D$2:$D$10)). This example assumes holidays are listed in D2:D10. NETWORKDAYS counts standard Monday-to-Friday workweeks; for a different weekend pattern, Excel also provides NETWORKDAYS.INTL, which accepts weekend settings.
Choose how to handle dates that have passed
There is no single best treatment for an overdue date. A negative count preserves useful information: a result of -3 means the date was three calendar days ago. This is often preferable in project tracking because it distinguishes an overdue task from one due today. The basic subtraction formula already behaves this way.
If the display should never go below zero, put the deadline in A2 and the nonnegative countdown in B2, then use =MAX(0,A2-TODAY()). This returns zero for a date in the past or today and a positive number for a future date. The trade-off is that it no longer tells you how late a past deadline is. You could keep a separate status column or preserve the signed day count elsewhere if that distinction matters.
For reporting, be explicit about what zero means. In the formula above, zero can mean the deadline is today, but it also means the deadline has passed. A separate status label, such as Due today or Overdue, can remove that ambiguity. Avoid treating a zero countdown as proof that a task is complete; the formula compares dates only and does not know the task’s status.
Dates with times and other countdown needs
If A2 contains a date and a time, the basic formula can return a decimal because Excel stores the time as a fraction of a day. For example, half a day is 0.5. A cell formatted with zero decimal places may display a rounded count that hides the fraction, but changing the number format does not change the underlying calculation.
If you want whole calendar days and A2 may contain a time, with the deadline in A2 and result in B2, use =INT(A2)-TODAY(). INT removes the time fraction by rounding the date-time serial down to its date. If you actually need hours and minutes until a specific appointment, use the exact current date and time instead: with the appointment date and time in A2 and the result in B2, enter =A2-NOW(). Format the result to show elapsed hours and minutes, for example with a custom duration format such as [h]:mm. This is a different calculation from whole days until a date.
A related misconception is that changing the result’s number format changes the number of days being counted. Formatting only changes how a value appears. It does not change whether weekends are included, turn a calendar-day count into a workday count, or freeze a TODAY() result. Choose the calculation first, then format its output for readability.
Common problems and fixes
If the formula returns #VALUE!, the target cell may contain text rather than a date Excel recognizes. Re-enter the date in a clear format, or convert the source data to real dates before subtracting. Imported dates are a common source of this problem: text can look identical to a date while remaining unusable in date arithmetic.
If the result looks like a date, change the result cell’s number format to Number or General. If the displayed count seems one day off, check the intended counting rule. Subtracting dates excludes today and gives the difference to the target date; counting both today and the target as calendar days is a different convention. Also verify that the deadline in A2 is the date you intended, especially where regional date formats could swap day and month.
If a copied formula keeps referring to the wrong deadline, check that the date reference is relative and that the formula is in the intended row. For a normal list, A2 should shift to A3 when the formula is filled down. By contrast, a shared holiday range should usually be absolute, as in $D$2:$D$10. A workbook can also use either the 1900 or 1904 date system; unusual date shifts when moving data between workbooks can be related to different date-system settings.
On Excel for Microsoft 365 for Windows, Mac, and Excel for the web, TODAY, date subtraction, and NETWORKDAYS are available. The formulas shown work across these platforms, though menus and keyboard shortcuts can differ. If a workbook is shared with older spreadsheet software, confirm that the recipient’s program supports the functions used.
Frequently Asked Questions
Does Excel have a DAYSUNTIL function?
No. A straightforward days-until calculation uses date subtraction: subtract TODAY() from the cell containing the target date. Excel’s DAYS function can also return the difference between two dates, but it is not necessary for this countdown.
Does the formula count today?
No. A2-TODAY() returns the difference between the target date and today, so a date that is today returns zero. If you want an inclusive count that treats today as day one, add one to the result, but do so only if that counting convention fits your use.
How do I count only weekdays until a date?
Use NETWORKDAYS with tomorrow as the start date and the target date as the end date: =NETWORKDAYS(TODAY()+1,A2), assuming the target is in A2. Add a holiday range as the third argument if you need to leave specific dates out. The function uses Saturday and Sunday as its standard weekend.
Why does my days-until result show a negative number?
The date in the target cell is earlier than today. That is expected behavior and indicates how many calendar days ago it was. If you prefer to display zero instead, use MAX to cap the result at zero, while remembering that this hides how overdue the date is.
Will the countdown update automatically tomorrow?
It updates when Excel recalculates the workbook; TODAY() is not a live clock that changes at midnight while the file is idle. Automatic calculation and reopening the workbook normally refresh the value. If you need a historical record, save a fixed value rather than relying on a formula that continues to update.
Related
- Monthly Excel Budget Format: Build a Simple Template
- Year Calendar in Excel: Create a 12-Month View
- When Was Excel Created? The 1985 Launch Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIF Excel Sum Range: How It Works and ExamplesIn Excel, SUMIF adds values that meet a condition: its range argument is where Excel checks the condition,…
- Power Query XLOOKUP From Another Table: How It WorksPower Query does not run the Excel XLOOKUP function inside a query. To return columns from another table,…
- ROUNDUP Excel Cell Reference: 10 Useful Formula PatternsWith 12.31 in A2, enter =ROUNDUP(A2,0) to return 13; the formula reads the number from a cell and…
- Text Formula Examples in Excel: Clean, Extract, CombineExcel text formulas let you combine words, extract part of a value, standardize capitalization, and clean imported text.…
- Filter Cells by Color in Excel: Steps and TipsTo filter cells by color in Excel for Microsoft 365 on Windows, select your data, choose Data >…
- Excel MID and FIND Functions: How to Extract Text in ExcelWith the text INV-2026-047 in A2, use =MID(A2,FIND("-",A2)+1,4) to extract the year; the result is 2026. MID returns…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.