Excel Number of Days Before Today: Formula and Examples

Formulas & functions · Updated November 19, 2026

To find how many days have passed since a date in Excel, subtract the date from TODAY(). If the date is in cell A2, enter =TODAY()-A2 in another cell; the result updates automatically as the current date changes.

The Basic Formula and a Worked Example

Excel stores dates as serial numbers, so subtracting one date from another returns the number of calendar days between them. For example, if A2 contains October 25, 2026, and today is November 3, 2026, the formula returns 9. It counts the elapsed days from the earlier date to today; it does not count October 25 as a full day in addition to that interval.

With the date to check in A2, enter this in B2:

=TODAY()-A2

Format B2 as General or Number if Excel displays a date instead of a day count. Select B2, then choose Home > Number Format and select General or Number. A date result usually means the formula is calculating but the result cell has a date format applied. You can copy the formula down column B to calculate the elapsed days for more dates in column A; Excel adjusts the row reference for each one.

The formula measures calendar days, including Saturdays, Sundays, and holidays. It gives a positive result for a date before today, zero for today, and a negative result for a future date. That behavior is often useful: a negative number makes it clear the date has not arrived yet.

Why TODAY() Makes the Result Change

TODAY() returns the current date according to the computer’s date when Excel recalculates the workbook. It does not store a permanent “today” value in the formula. Consequently, a result of 9 today could become 10 tomorrow without changing A2. This is helpful for tracking age of an invoice, days since an event, or time elapsed since a project milestone.

Because TODAY is a volatile function, Excel recalculates it when the workbook recalculates. If the number is used in a report that must preserve the date of calculation, record that date as a fixed value rather than relying on TODAY(). For instance, you might enter the report date in a separate cell and subtract the historical date from that cell. That keeps a saved report reproducible instead of letting its day counts shift later.

For a one-time calculation, you can also replace the TODAY part with a date typed into the formula, but a cell containing a clearly labeled “As of” date is easier to maintain and audit. If Excel’s calculation mode is set to Manual, formulas may not refresh until recalculation. On Windows, press F9 to recalculate formulas; on a Mac, use Fn+F9 on keyboards where the function keys control hardware features, or choose Formulas > Calculate Now.

Enter and Format Dates Correctly

Excel must recognize A2 as a date value for subtraction to work as intended. Enter dates in a format Excel recognizes for your regional settings, such as 11/3/2026 in a month/day/year setting or 3/11/2026 in a day/month/year setting. Ambiguous entries can be interpreted differently across computers, so a month name such as 3-Nov-2026 can make the intended date clearer.

A date that is stored as text may produce a #VALUE! error or behave unexpectedly. Common signs include the value aligning differently from nearby dates or failing to change when you apply a date format. Re-entering the date in a recognized format often fixes it. You can also use Data > Text to Columns to convert consistently formatted text dates, but check the preview and date order before finishing so Excel does not swap the month and day.

  • Keep the original dates in one column and the calculated day counts in another; this makes it easier to inspect the inputs.
  • Use a consistent date format for readability, such as a four-digit year, especially when sharing the workbook.
  • If the source includes date and time values, decide whether the time should affect the calculation before choosing a formula.

Changing a cell’s display format changes how its value appears, not the underlying date. If a result looks wrong, check both the formula and the stored values rather than assuming the displayed format is the cause.

Handle Blank Cells and Future Dates

If A2 is blank, the basic subtraction can treat it like Excel’s zero date and show a very large number. To leave the result blank until a date is entered, use this formula in B2, with the date in A2:

=IF(A2="","",TODAY()-A2)

This checks whether A2 is empty before calculating. It is useful in a list that is still being filled in, because it avoids distracting results for unused rows. The formula returns an empty string for a blank cell, which looks blank but is technically a formula result. If other formulas need to distinguish truly empty cells from formula-generated blanks, account for that difference.

A future date returns a negative number with the basic formula. That may be exactly what a schedule needs. If your report should show zero rather than a negative number for future dates, use the following version, again assuming the date is in A2:

=IF(A2="","",MAX(0,TODAY()-A2))

This deliberately caps the result at zero. It does not tell you that the date is in the future, so do not use it when that distinction matters. A separate status column or conditional formatting can make upcoming dates visible instead of hiding them. Avoid using a formula that silently changes future dates into elapsed-day counts; it is better to decide and document how the workbook should treat them.

Calendar Days, Inclusive Counts, and Business Days

“Days before today” can mean different things depending on the task. The standard subtraction counts the interval between two dates. If you are counting dates inclusively—for example, treating both the starting date and today as days in a run—you may want to add 1. With the date in A2, use:

=TODAY()-A2+1

For October 25 through November 3, 2026, inclusive, this returns 10 rather than 9. If A2 is today, it returns 1. Inclusive counting is common in some schedules and duration descriptions, but it is not the same as elapsed time. Choose the convention that matches the question and use it consistently.

For working days rather than calendar days, Excel’s NETWORKDAYS function counts weekdays between two dates, including both endpoints when they are working days. With a starting date in A2 and no holiday list, this formula counts working days from A2 through today:

=NETWORKDAYS(A2,TODAY())

That result is not automatically “completed working days before today”: it can include the starting date and today. If you need to exclude one or both endpoints, define that rule carefully and adjust the dates being counted. To exclude listed holidays, put holiday dates in F2:F20 and use:

=NETWORKDAYS(A2,TODAY(),$F$2:$F$20)

Make sure the holiday entries are real Excel dates, not text. NETWORKDAYS uses Saturday and Sunday as the weekend by default. For a different weekend pattern, Excel also provides NETWORKDAYS.INTL, which lets you specify weekend days; check its weekend-code options against the workweek you actually use.

Dates That Include Times

Excel stores times as fractions of a day. If A2 contains November 2, 2026 at 6:00 p.m., subtracting it from TODAY() at midnight can produce a fractional result, such as 0.25 for a quarter of a day, rather than a whole number. A cell formatted as Number may show decimals; a cell formatted as General may show a decimal or a rounded-looking value depending on its display width.

If you want to ignore the time and compare calendar dates only, use INT to remove the fractional time. With a date or date-time value in A2, use:

=TODAY()-INT(A2)

For a list that may contain blanks, combine that approach with the blank check:

=IF(A2="","",TODAY()-INT(A2))

This treats any time on a given date as that date. If the exact hours and minutes matter—for example, measuring how long a process has been running—do not discard the time. Instead, use NOW() for the current date and time, then decide whether to display the result as a decimal, hours, or another unit. TODAY() is appropriate when the question is specifically about dates, not precise elapsed hours.

Common Errors and Useful Checks

If the answer seems implausible, check the input and the counting rule before rewriting the formula. Several common issues account for most unexpected results:

  • A date appears instead of a number: change the result cell’s number format to General or Number.
  • A very large result appears for an empty row: add a blank-cell check such as the IF formula above.
  • The result is negative: the date in A2 is later than today; confirm whether that is expected.
  • The formula returns #VALUE!: A2 may contain text that Excel cannot interpret as a date.
  • The result changes by a fraction: A2 may include a time component; use INT if you want whole calendar-date differences.

Another frequent misunderstanding is that subtracting dates automatically excludes weekends or counts the starting day. It does neither: simple subtraction gives the number of calendar-day intervals. NETWORKDAYS handles weekdays, while adding 1 changes the calculation to an inclusive calendar-date count. These are different measures, so name the result column accordingly—for example, “Elapsed calendar days” or “Weekdays, endpoints included.”

Frequently Asked Questions

What formula counts days since a date in Excel?
With the earlier date in A2, enter =TODAY()-A2. It returns the calendar-day difference and updates as the current date changes.

How do I count days before today but leave future dates blank?
With the date in A2, use =IF(A2>=TODAY(),"",TODAY()-A2). It returns a blank for today or a future date and a positive count for earlier dates. If you want today to return zero, change the comparison to A2>TODAY().

Does Excel count today as one of the days?
No. =TODAY()-A2 counts elapsed intervals, so a date equal to today returns 0. Add 1 when an inclusive count is intended; then a date equal to today returns 1.

How can I count weekdays instead of calendar days?
Use =NETWORKDAYS(A2,TODAY()) to count weekdays between the start date and today, including eligible endpoints. Supply a holiday range as the third argument if those dates should be excluded.

Why does the formula show a decimal?
The source cell may include a time, which Excel stores as part of a day. If you only need whole calendar days, use =TODAY()-INT(A2) to ignore the time component.

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.