Excel Formula to Subtract Dates From Today: Days and More

Formulas & functions · Updated November 19, 2026

To find how many days have passed since a date in cell A2, use =TODAY()-A2. To find how many days remain until a date in A2, reverse the order: =A2-TODAY(); both formulas return a number of days, and future or past dates can produce negative results depending on the direction.

The basic formula and a quick example

For the elapsed-days calculation, assume A2 contains a real Excel date, such as March 1, 2025. In another cell, enter =TODAY()-A2. If today is March 15, 2025, the result is 14: March 1 to March 15 is a difference of 14 days. The result will update as the current date changes, so the same formula will show a different number later.

For a deadline in the future, assume A2 contains March 20, 2025. Enter =A2-TODAY(). If today is March 15, 2025, the result is 5 days remaining. These dates are illustrative; your workbook uses the actual date returned by TODAY(). In Microsoft 365 for Windows, type the formula in a blank cell and press Enter. The formulas work the same way in Excel for Mac and Excel for the web.

To apply the calculation to a list, put the dates in A2:A20 and enter the appropriate formula beside the first date, such as =TODAY()-A2 in B2. Fill or copy B2 down the column. Excel changes the relative reference from A2 to A3, A4, and so on, while TODAY() continues to refer to the current date.

How Excel date subtraction works

Excel stores dates as serial numbers: whole numbers represent days, and a time of day is stored as a fraction of a day. Subtracting one date from another therefore returns the number of days between their underlying values. For example, a difference of 14 means 14 days, while a difference of 0.5 represents half a day if time values are involved.

TODAY() returns the current date according to Excel’s calculation environment, with no time component. It is a function, so it takes empty parentheses. Because it is a volatile function, Excel can recalculate it when the workbook recalculates; the displayed result is not a permanent record of the date when you first entered the formula. If you need to preserve a calculation as a fixed value, copy the result and use Paste Special > Values, or type the date you want to use as a fixed reference.

A cell that looks like a date may instead contain text, especially if it was imported from another system or entered in a format Excel did not recognize. Text does not always behave like an Excel date in subtraction. A quick check is to select the cell and look at its alignment and number format, but appearance alone is not conclusive. If the subtraction returns an error, confirm that the value is a real date and that the workbook’s regional date settings match the way the date was entered.

Choose the subtraction order for your question

Subtraction is directional. Use the date on the right as the starting point and the date on the left as the ending point if you want a positive result for a later date. For a date in the past, TODAY() minus the stored date gives a positive elapsed-day count. For a future deadline, the stored date minus TODAY() gives a positive countdown. Switching those orders gives a negative result, which is mathematically correct but may not be the display you expect.

  • Days since a past event: assume A2 holds the event date; use =TODAY()-A2.
  • Days until a future event: assume A2 holds the deadline; use =A2-TODAY().
  • Difference regardless of which date is earlier: assume A2 and B2 hold two dates; use =ABS(B2-A2). ABS makes the result nonnegative, but it does not tell you which date came first.

A negative result can be useful. For example, a countdown formula becoming negative shows that the deadline has passed and by how many days. If you want overdue items to display as a positive number of days overdue instead, use the elapsed-days direction for those dates. Decide what a negative value should mean before changing the formula, so that the result remains interpretable.

Calendar days, inclusive counts, and completed days

Ordinary date subtraction measures the gap between two dates, not the number of calendar labels counted inclusively. From March 1 to March 15 is 14 elapsed days, even though counting both March 1 and March 15 as included dates gives 15 dates. This distinction matters for attendance logs, project schedules, and rules that define a period as including both its start and end dates.

If an inclusive count is genuinely what you need, assume A2 is the first date and today is the last included date; use =TODAY()-A2+1. With A2 set to March 1 and an illustrative current date of March 15, the result is 15. Do not add 1 automatically to every date difference: for measuring elapsed time, the unadjusted subtraction is normally the right calculation.

For a date in the future, the same inclusive idea needs to match your definition of the period. A countdown often reports the number of days between today and the deadline, without counting today as a full remaining day. If a policy instead counts both today and the deadline, it may require an inclusive adjustment. Check the intended counting convention rather than relying on a formula that merely looks familiar.

Calculate full months or years instead of days

Subtracting dates gives days, not calendar months or years. Dividing the day count by 30 or 365 is only an approximation because months have different lengths and leap years affect annual totals. For completed calendar months between an earlier date and today, assume A2 contains a date on or before today and use =DATEDIF(A2,TODAY(),"m"). DATEDIF counts only whole months that have elapsed.

For completed years, with the same assumption that A2 is no later than today, use =DATEDIF(A2,TODAY(),"y"). For example, if A2 is June 10, 2020 and the illustrative current date is June 9, 2025, the result is 4 completed years; the fifth anniversary has not arrived yet. DATEDIF can return an error if its start date is later than its end date, so check the order when using it with dates that may be in the future. If you need a duration expressed as years and remaining months, that is a different reporting requirement from a single total-month or total-year count.

Count working days instead of calendar days

For a schedule that excludes weekends, Excel’s NETWORKDAYS function counts working days between two dates. Assume A2 contains the start date and B2 contains the end date; use =NETWORKDAYS(A2,B2). The function includes qualifying weekdays at both ends of the range, so the result is not the same as simply subtracting the dates. Saturday and Sunday are treated as weekends by default.

You can also exclude listed holidays. Assume A2 contains the start date, B2 the end date, and F2:F10 the holiday dates; use =NETWORKDAYS(A2,B2,F2:F10). Each holiday must be stored as a valid Excel date to be recognized. NETWORKDAYS is useful for a work schedule or business-day estimate, but confirm whether your organization treats different days as weekends or uses additional nonworking dates. If weekend days vary, Excel also has NETWORKDAYS.INTL, which allows a weekend pattern to be specified.

Make the result display as a number

A correct date-difference formula can appear wrong if its result cell is formatted as a date. Since Excel stores dates as numbers, a result such as 14 may display as a date in a date-formatted cell. Select the result cells and choose Home > Number Format dropdown > General or Number. In Excel for Mac, use the Home tab’s number-format controls as well; names and placement can vary slightly by build. For a plain count of days, General or Number is usually suitable.

If you want a decimal count because the source values contain times, use a number format that shows decimal places. If you want whole days only, decide whether to discard a partial day or round it, rather than hiding the decimal through formatting and assuming it is not present. Date formatting affects how a value is shown, not the underlying arithmetic. Likewise, changing a cell’s appearance to a date format does not convert text into a true date.

Common problems and how to fix them

  • The result is a date, not a day count: change the result cell’s number format to General or Number.
  • The result is negative: check which date you subtracted from which. A future date minus today is positive; today minus a future date is negative.
  • The formula shows #VALUE!: inspect the source cell for text, spaces, or an unrecognized date. Re-enter or convert the value to a valid Excel date.
  • The answer includes a fraction: a source value likely includes a time. Remember that a time is a fraction of one day, even if the cell’s format hides it.
  • The result does not appear to update: check that calculation is set to Automatic on the Formulas tab under Calculation Options. In Manual mode, formulas may wait for recalculation.

Be especially careful with imported dates such as 03/04/2025. Depending on regional settings, that text may be interpreted as March 4 or April 3. Confirm the intended date before calculating; a perfectly valid subtraction can still produce the wrong business answer if the input date was interpreted incorrectly. When a date is supplied as separate year, month, and day values, the DATE function can construct a date reliably, but imported text may need conversion based on its actual source format.

Frequently Asked Questions

What formula gives the number of days between a date and today?
For days elapsed since a date in A2, subtract A2 from TODAY(). Reverse the order if you want a positive countdown to a future date.

Does subtracting dates count both the start and end dates?
No. Basic subtraction returns the elapsed-day difference. Add one only when your task explicitly requires an inclusive count that includes both endpoints.

Why does Excel show a date after I subtract dates?
The result cell is probably formatted as a date. Change its number format to General or Number to display the day count.

Can I calculate business days from today?
Yes. NETWORKDAYS counts weekdays between dates and can omit holidays listed in a range. Its endpoint-counting rules may differ from the elapsed calendar-day difference, so check which dates should be included.

Will TODAY() update automatically tomorrow?
It updates when Excel recalculates the workbook. Automatic calculation is the usual setting, but a workbook in Manual calculation mode may need recalculation before the displayed date changes.

A practical way to choose

Start by deciding whether you need elapsed calendar days, a future countdown, an inclusive date count, completed months or years, or working days. Then confirm that the input is a real date, choose the subtraction order that matches the question, and format the answer as a number when you want a day total. For a simple difference, =TODAY()-A2 is usually all you need; the alternatives are for cases where the meaning of “days” or the direction of the calculation is different.

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.