Excel Number of Days Between Dates Today: Formulas

Formulas & functions · Updated November 19, 2026

To find how many days have passed between a date in Excel and today, enter =TODAY()-A2, assuming the earlier date is in cell A2. The result updates when Excel recalculates; use =A2-TODAY() instead to count down to a future date.

The simplest formula for days from a date to today

In Microsoft Excel for Microsoft 365 on Windows, type the date you want to compare into A2, then enter the formula in another cell, such as B2. Excel stores dates as numbers, so subtracting the date in A2 from today’s date returns the number of calendar days between them. If A2 contains October 15, 2026, and today is November 3, 2026, the result is 19.

With the date to check in A2, use =TODAY()-A2. A positive result means the date in A2 is in the past, zero means it is today, and a negative result means it is in the future. The TODAY function takes no arguments and returns the current date according to the computer’s date and time settings.

If you want the distance between the two dates regardless of which one comes first, use the absolute value. With the date in A2, enter =ABS(TODAY()-A2). For example, a date 10 days in the future returns 10 rather than −10. This removes the useful distinction between past and future, so it is best when you only care about the size of the gap.

How Excel counts days between dates

Excel dates are stored as serial numbers, with each whole-number step representing one calendar day. Subtraction therefore gives elapsed calendar days, not the number of work shifts, nights, or hours. For instance, subtracting a Monday date from the following Monday returns 7, even though five weekdays fall between those dates.

TODAY() supplies a date without a time component. It is a dynamic function: the displayed answer can change when the workbook recalculates on a later day. It does not continuously tick forward every second. Excel’s calculation settings and actions such as opening or editing a workbook can trigger recalculation. For normal use, check that calculation is set to Automatic under Formulas > Calculation Options > Automatic.

A date difference is not automatically inclusive of both endpoint dates. Subtracting October 15 from November 3 returns 19, because it counts the number of midnight-to-midnight boundaries crossed. If a report’s convention is to count both the starting date and today, add one. With the start date in A2, use =TODAY()-A2+1. Under the same October 15 to November 3 example, this returns 20. Decide which convention matches the task before comparing totals.

Choose the right formula for your question

“Days between dates” can mean different things in calendars, project schedules, and countdowns. Choose a formula based on what the result is meant to describe:

  • Elapsed calendar days since a date: with the earlier date in A2, use =TODAY()-A2. Past dates produce positive values.
  • Days remaining until a date: with the future date in A2, use =A2-TODAY(). A date that has already passed produces a negative value.
  • Unsigned distance from today: with either a past or future date in A2, use =ABS(TODAY()-A2).
  • Inclusive count of dates in a past period: with the start date in A2, use =TODAY()-A2+1, if the start date and today should both count.

For a fixed comparison between two dates rather than today, put the start date in A2 and the end date in B2, then enter =B2-A2. This is often more suitable for a report that must remain unchanged after it is created. If the dates are reversed, the result is negative; swap the references or use ABS if direction does not matter.

Use DATEDIF or a fixed as-of date

DATEDIF is another option when you want a formula that explicitly names the unit being counted. With a start date in A2 and a date no earlier than A2 as the end date, use =DATEDIF(A2,TODAY(),"d"). The "d" unit requests the number of complete days. For ordinary calendar-day subtraction, this gives the same result as TODAY()-A2.

DATEDIF is an older compatibility function that Excel does not prominently document in the function autocomplete, but it remains available in Excel. Its start date must be earlier than or equal to its end date; otherwise, it can return a number error. The direct subtraction formula is easier to adapt when you need a negative result for future dates, so it is usually the clearest choice for a simple day count.

A fixed as-of date is useful for reproducible examples or reports. Put a start date in A2 and a chosen comparison date in B2, then enter =B2-A2 in C2. For example, with October 15, 2026 in A2 and November 3, 2026 in B2, C2 returns 19. Unlike TODAY(), the value in B2 stays fixed until someone changes it. You can label B2 “As of” so readers know what date the calculation uses.

Calendar days versus working days

The basic subtraction formula includes weekends and holidays. If you are measuring business days—for example, weekdays in a delivery estimate—use NETWORKDAYS instead. With a start date in A2 and the end date set to today, enter =NETWORKDAYS(A2,TODAY()). This function counts working days from the start date through the end date, including both endpoints when they are working days. That inclusive behavior means it is not interchangeable with a simple elapsed-day difference.

To exclude company holidays too, list holiday dates in D2:D10. With the start date in A2, today as the end date, and holidays in D2:D10, use =NETWORKDAYS(A2,TODAY(),D2:D10). The holiday cells need to contain real Excel dates, not date-looking text. NETWORKDAYS uses a Saturday-and-Sunday weekend by default; NETWORKDAYS.INTL is available when a different weekend pattern is needed. A business-day answer and a calendar-day answer address different questions, so state which one a spreadsheet reports.

Format the result and check the input dates

Sometimes a correct subtraction appears as a date, such as January 19, 1900, rather than as 19. The calculation may be right: Excel is displaying the numeric result using a date format. Select the result cell and choose Home > Number Format > General or Number. You can also use the Number Format drop-down on the Home tab. The result should display as a number of days.

If the formula returns an error or an unexpected value, check the inputs before changing the calculation:

  • Confirm that A2 contains a real Excel date. A date stored as text may not subtract as expected.
  • Check the order of subtraction. TODAY()-A2 measures from the cell date to today; A2-TODAY() reverses the sign.
  • Look for hidden time values. If A2 includes both a date and a time, subtraction can produce a decimal result. For a calendar-date count, with the date-time value in A2, use =TODAY()-INT(A2).
  • Check whether the workbook is using manual calculation. A result based on TODAY() can appear out of date until recalculation occurs.
  • Make sure the cell reference points to the intended date, especially if you copied the formula down a column.

Date text can be interpreted according to regional settings. A value such as 04/05/2026 may mean April 5 or May 4 depending on the date convention in use. To avoid ambiguity in formulas or when building dates from separate year, month, and day values, use the DATE function. With the year in A2, month in B2, and day in C2, enter =DATE(A2,B2,C2). For a fixed date, =DATE(2026,10,15) creates October 15, 2026, regardless of the computer’s display order.

When to use a static date instead of TODAY

TODAY() is convenient for live countdowns, aging lists, and dashboards because it refreshes to the current date as the workbook recalculates. It is less suitable when you need to preserve the answer as it was on a particular day. An invoice-age report opened next month will show a larger age if its formulas still refer to TODAY(). For an audit trail or monthly snapshot, record the as-of date in a cell and use that cell in the subtraction, rather than relying on a moving current date.

Excel’s date system also has historical limits and workbook settings that can affect date serials, especially when dates are copied between workbooks configured with different date systems. For ordinary modern dates within one workbook, this rarely changes the basic formula. If a copied date is off by a fixed number of days, check the workbook date-system setting and verify the displayed dates before trusting the calculation.

Frequently Asked Questions

How do I calculate the number of days between two dates in Excel?
Put the start date in A2 and the end date in B2, then enter =B2-A2 in another cell. The result is the elapsed calendar-day difference; reverse the references if you want the sign reversed.

Does Excel count today as one of the days?
No. TODAY()-A2 counts elapsed days and does not automatically count both endpoint dates. For an inclusive count of a past period that includes the start date and today, use =TODAY()-A2+1.

How do I show days remaining until a deadline?
Put the deadline in A2 and enter =A2-TODAY(). A positive number means the deadline is in the future, zero means it is today, and a negative number means it has passed.

Why does my days-between-dates formula show a date?
The result cell is probably formatted as a date. Change its number format to General or Number from the Home tab. Excel is displaying the subtraction result with a date appearance rather than showing the day count directly.

Does TODAY update automatically in Excel?
TODAY() returns the current date when Excel recalculates the workbook. With calculation set to Automatic, it normally refreshes as the workbook is recalculated; it is not a continuously updating clock.

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.