To calculate the number of days from a date in Excel to today, subtract the date cell from TODAY(). If the starting date is in A2, enter =TODAY()-A2; format the result as a number if Excel displays it as a date. The result updates when Excel recalculates, so it always reflects the current date.
The basic TODAY formula
Excel stores dates as serial numbers, which lets it perform arithmetic on them. Subtracting an earlier date from a later date gives the number of elapsed calendar days between them. For a date in A2, the formula is:
With the date to measure from in A2, enter =TODAY()-A2 in another cell. If A2 contains October 31, 2026, and TODAY() returns November 3, 2026, the result is 3 days.
This counts the difference between the two date values, not both dates as separate days. October 31 to November 1 is one elapsed day, even though two calendar dates appear in the sequence. That distinction matters for deadlines, age calculations, project durations, and any task where someone may mean an inclusive count instead.
TODAY() takes no arguments: type the opening and closing parentheses even though nothing goes between them. The function returns the current date according to the computer or service running Excel, without a time component. The result can change on a later recalculation or when the workbook is reopened.
Set up the worksheet step by step
- Enter a valid date in A2. You can type a date such as 10/31/2026, or use a date format appropriate to your regional settings.
- Select an empty cell where you want the day count to appear.
- Type
=TODAY()-A2and press Enter. - If you have more dates in A3, A4, and so on, copy the formula down. Excel adjusts the cell reference for each row.
- If the result looks like a date instead of a number, change the result cell to General or Number format.
For a table of due dates, put each date in column A and the formula in column B. A row with a due date of November 10, 2026, will show a negative number on November 3, 2026, because the date is still seven days in the future. A positive result means the date has passed; zero means the date is today.
Handle future dates, blank cells, and inclusive counts
A negative number is mathematically correct when the date in A2 is later than today. If the goal is a countdown until that future date, reverse the subtraction. With the target date in A2, use =A2-TODAY(). If A2 is November 10, 2026, and today is November 3, the result is 7. Once the target date passes, the result becomes negative.
To show only the number of days remaining and never a negative value, use MAX. With the target date in A2, enter =MAX(0,A2-TODAY()). This returns 7 for November 10 when today is November 3, and returns 0 on or after the target date. Choose this approach only if a past-due status should be hidden; otherwise, a negative number communicates that the date has passed.
Blank rows can otherwise produce confusing results. A blank cell treated as zero may lead to a very large day count. With the date in A2, use =IF(A2="","",TODAY()-A2) to leave the result blank until A2 has an entry. If a cell contains text that looks like a date but Excel has not recognized it as a date, the calculation may return an error rather than a useful count.
If you intend to count both the starting date and today, add one. With the start date in A2, enter =TODAY()-A2+1. For October 31 through November 3, inclusive, that returns 4. Inclusive counting is a convention for particular tasks, not a correction to the basic formula. State which convention you use when sharing a schedule or report.
Choose the right meaning of days between dates
Before choosing a formula, decide what the count represents. Calendar-day difference is appropriate for elapsed time: subtract the earlier date from the later one. For a starting date in A2 and an ending date in B2, use =B2-A2. If A2 is November 1 and B2 is November 8 of the same year, the result is 7 elapsed days. If the order of dates might vary and you always need a nonnegative distance, use =ABS(B2-A2) with those same cells.
For a countdown or an age-in-days style measure that ends today, use TODAY() as the ending date. For a duration that should count the first and last calendar dates, add one after subtracting. For a business schedule, use a workday function instead of treating every calendar day equally. These are different definitions, so there is no single formula that is correct for every use.
Be careful with age questions: days elapsed since a birth date is not the same as age in whole years. For an exact number of elapsed days, subtraction is suitable. For a person’s age in completed years, use a year-based calculation designed for that purpose rather than dividing the day count by 365; leap years mean that shortcut can be inaccurate.
Count working days instead of calendar days
NETWORKDAYS counts weekdays from Monday through Friday and can exclude listed holidays. It includes the start and end dates when they are working days. For a start date in A2, an end date of today, and holiday dates in H2:H10, enter =NETWORKDAYS(A2,TODAY(),$H$2:$H$10). If there are no holidays to exclude, the holidays argument can be left out: =NETWORKDAYS(A2,TODAY()).
For example, if the start date is Monday, March 2, 2026, and the end date is Friday, March 6, 2026, NETWORKDAYS returns 5 when there are no holidays in that span. This is an inclusive count of workdays, unlike the simple subtraction result of 4 elapsed days. A holiday listed in the supplied range is removed from the count, provided it falls on a weekday in the interval.
If weekends do not fall on Saturday and Sunday, use NETWORKDAYS.INTL, which allows a weekend pattern to be specified. The weekend argument has several supported numeric patterns and can also be supplied as a seven-character string of zeroes and ones; check Excel’s function help for the pattern that matches the organization’s schedule. Use the same holiday range idea if relevant. These functions count workdays; they do not calculate business hours or account for partial working days.
Format the answer and understand recalculation
Date subtraction produces a number of days, but Excel may apply a date format to the result cell. If a result such as 3 appears as a date in the year 1900, the arithmetic may be fine: Excel is displaying the number 3 as a date serial. Select the result cell, then choose Home > Number Format and select General or Number. Increase or decrease decimal places if needed; date-only subtraction should ordinarily produce a whole number.
TODAY() is a volatile function, meaning Excel recalculates it as part of normal recalculation rather than keeping the original date forever. It is useful for live aging reports and countdowns, but it is not a permanent timestamp. A workbook opened next week will normally show a different result for the same start date. Pressing F9 in Excel for Windows requests recalculation; on a Mac, the equivalent recalculation shortcut is usually Fn+F9, depending on keyboard settings. Excel can also be set to calculate automatically or manually under Formulas > Calculation Options.
If a report must preserve the day count as of a particular date, use a fixed as-of date in a cell rather than relying on TODAY(). For example, place the chosen reporting date in B1 and reference it in the calculation. With the original date in A2 and the fixed as-of date in B1, use =$B$1-A2. The dollar signs keep B1 fixed when you copy the formula down. This makes the result reproducible until someone changes the reporting date.
Common errors and how to fix them
- A date displays as a large number: Excel dates are serial values. Apply a date format to the input cell if you need to inspect the date, or Number/General to the calculated day count.
- The answer is negative: The date being subtracted is later than the ending date. Decide whether that should be a negative overdue/early indicator or whether the subtraction order should be reversed.
- The formula returns #VALUE!: One of the inputs may be text rather than a real Excel date. Re-enter it using a recognized date format, or convert the source data before calculating.
- A blank row shows an enormous result: Add the IF blank check shown above, or keep the formula out of unused rows.
- The answer is off by one: Check whether the task asks for elapsed days or an inclusive count of calendar dates. Add 1 only for the latter, and remember that NETWORKDAYS uses its own inclusive workday convention.
- The result did not change at midnight: TODAY updates through recalculation, not necessarily at the exact instant midnight passes. Confirm calculation is automatic or trigger a recalculation.
For dates imported from another system, also check whether the values include times. A date-time value can contain a fractional part, so subtraction may produce a decimal day count. If you only need whole calendar-date differences, remove or ignore the time component in the source data before interpreting the result. Do not round blindly if times are meaningful to the task.
Frequently Asked Questions
What is the Excel formula for days between a date and today?
With the earlier date in A2, use =TODAY()-A2. It returns elapsed calendar days and updates when Excel recalculates.
How do I count days until a future date?
With the future date in A2, use =A2-TODAY(). A positive result is the number of days remaining; a negative result means the date has passed.
How do I count both dates?
For an inclusive calendar-day count from A2 through today, use =TODAY()-A2+1. Inclusive counts depend on the task’s convention, so label the result clearly.
Can Excel count weekdays between a date and today?
Yes. With the start date in A2, use =NETWORKDAYS(A2,TODAY()) for Monday-to-Friday workdays. Supply a holiday range as the third argument if holidays should be excluded.
Why does my formula show a date instead of a number?
The result cell is probably formatted as a date. Change its number format to General or Number; the underlying day difference may already be correct.
Does TODAY() update automatically?
It updates when Excel recalculates the workbook. It is not a fixed date stamp, so use a manually entered as-of date in a cell when a report must retain its original calculation date.
Related
- Excel Color Scales: How Conditional Formatting Works
- Close Excel Shortcut Key for Windows and Mac
- Close Group Excel Shortcut: Close Workbooks and Rows
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Dynamic Named Range Excel Formula: Setup and ExamplesA dynamic named range in Excel is a named reference that expands or contracts as entries are added…
- Excel: How Many Days Are in a Month? Formula GuideA month can have 28, 29, 30, or 31 days, so Excel needs a year and month—or a…
- Excel INDEX Formula Examples for Rows, Columns, and MatchesExcel’s INDEX function returns the value at a chosen position in a range. With values in A2:C5, =INDEX(A2:C5,3,2)…
- How to Copy Formula Data in Excel Without Breaking ReferencesTo copy formula data in Excel, select the cells, press Ctrl+C, choose the destination, and use Home >…
- Excel CONCAT and Text: Formulas, Examples, and FixesTo join text and cell values in Excel, use CONCAT or the ampersand operator. For example, if A2…
- Remove Spaces in One Excel Cell: Formula and StepsTo remove every ordinary space from one Excel cell, enter =SUBSTITUTE(A1," ","") in a nearby blank cell, replacing…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.