To calculate how many days have passed from a date in cell A2 to today, enter =TODAY()-A2 in another cell. Excel returns a positive number for a past date and a negative number for a future date; the result updates when the worksheet recalculates.
The Basic TODAY Formula
This is the simplest way to measure the calendar-day difference between a date and today. The formula assumes the date you want to compare is in A2; enter it in a different cell, such as B2. If A2 contains November 1, 2026, the result on November 3, 2026, is 2. The count is the number of midnight-to-midnight date boundaries between the two dates, not an inclusive count of both dates.
Make sure A2 contains an actual Excel date, rather than text that only looks like a date. You can enter a date in the cell or select it from a date picker where available. Excel stores dates as serial numbers, so subtracting one date from another gives the number of days between their serial values. The cell format does not change the calculation, but it can change how the result appears.
What TODAY() Does
TODAY() returns the current date according to Excel’s calculation environment, without a time value. It is a function with no arguments, so its parentheses remain empty. Because the date is supplied by the function rather than typed into the formula, the result can change from one day to the next.
TODAY is not a live clock that refreshes every second. Excel recalculates it when the workbook recalculates, for example after a relevant edit, when the workbook opens, or when calculation is triggered. In Excel for Microsoft 365 on Windows, check that automatic calculation is enabled under Formulas > Calculation Options > Automatic. Excel for Mac has the corresponding calculation options; Excel for the web recalculates formulas in the workbook as it is edited.
This behavior makes TODAY useful for dashboards, aging lists, reminders, and status columns that should stay current. It is less suitable when a report must retain the exact date on which it was prepared. In that case, record a fixed date as a value rather than relying on a formula that can recalculate later.
How to Choose the Right Day Count
The basic subtraction formula counts elapsed calendar days. That is generally what people mean by “days between dates,” but different tasks can use different counting conventions. Decide whether you need elapsed days, an inclusive range, or a business-day count before choosing the formula.
- Elapsed days: Use date subtraction when the question is how many full calendar-date steps separate a date from today. A date two days ago produces 2.
- Inclusive date count: Add 1 if the task counts both the starting date and today as days in the period. A period from November 1 through November 3 has three included dates, even though only two days have elapsed.
- Business days: Use a workday-counting function when weekends should not count. Public holidays need to be included separately if they should also be excluded.
- Time elapsed: If either cell includes a time as well as a date, decide whether fractions of a day matter. Ordinary date subtraction can return a decimal.
Worked Examples
Suppose A2 contains November 1, 2026, and today is November 3, 2026. With the date to compare in A2, =TODAY()-A2 returns 2. If A2 instead contains November 5, 2026, the same formula returns -2 because that date is two days in the future. A negative result is mathematically correct; it is not automatically an error.
If you want the distance between the dates regardless of which one is earlier, use an absolute difference. With the comparison date in A2, =ABS(TODAY()-A2) returns 2 for either November 1 or November 5 when today is November 3. This removes the direction of the difference, so it does not tell you whether the date is past or upcoming.
Excel also has a function that expresses the order of the subtraction explicitly. With the earlier or comparison date in A2, =DAYS(TODAY(),A2) returns the same signed result as subtraction: 2 for November 1, 2026, on November 3, and -2 for November 5. The first argument is the end date and the second is the start date. For a quick formula, direct subtraction is often easier to recognize; DAYS can make the intended order clearer to another reader.
For two dates that are both in cells, put the earlier date in A2 and the later date in B2. Enter =B2-A2 to calculate the elapsed calendar days from A2 to B2. For example, November 1 in A2 and November 3 in B2 return 2. Reversing the references returns -2, which is useful when the sign should indicate which date comes first.
Counting Both the Start and End Dates
Subtraction does not count both endpoints as separate days. From November 1 to November 3, the elapsed difference is 2, while an inclusive list of dates contains November 1, 2, and 3, for a total of 3. This distinction often causes confusion in booking periods, project schedules, and date ranges.
If your intended rule is to include the starting date and today, with the start date in A2, use =TODAY()-A2+1. On November 3, 2026, with November 1 in A2, that returns 3. Do not add 1 automatically: for age calculations, elapsed time, or many deadline checks, the unadjusted difference is the appropriate count. If the start and end can be reversed, define how inclusive counting should work before adapting the formula.
Formatting and Dates That Include Times
A date subtraction result is a number, but Excel may display it using the format inherited from the formula cell. If the result looks like a date instead of a day count, select the result cell and choose General or Number from the Number Format control on the Home tab. You can also increase or decrease decimal places if needed. Formatting changes appearance, not the underlying arithmetic.
Date-time values contain a fractional part for the time of day. If A2 contains November 1 at 12:00 p.m., while TODAY returns midnight on November 3, the raw difference is 1.5 days. That is correct for elapsed time, but a reader expecting whole calendar dates may find it surprising. To compare only the date portion of a timestamp, with the date and time in A2, use =TODAY()-INT(A2). For November 1 at noon and a current date of November 3, this returns 2.
If you need elapsed hours or partial days, keep the fraction and format the result as a number. If you need a whole-day count from timestamps, be precise about whether you mean calendar dates touched, complete 24-hour periods, or rounded days; those are different rules. Simply changing the display to zero decimal places can hide a fraction without changing the calculation.
Common Problems and Fixes
- The result is negative: The date in A2 is later than today. Keep the negative sign if future dates should be distinguishable, or use an absolute difference only when direction does not matter.
- The result looks like a date: Change the output cell’s number format to General or Number. Excel may have applied a date format to the formula cell.
- The formula returns an error or an unexpected value: Check that the source is a real date value, not text. Date text can be interpreted differently depending on regional settings, such as whether 04/05 means April 5 or May 4. Re-enter the date in the intended locale or convert the text to a proper date value.
- The count is off by one: Decide whether the task asks for elapsed days or counts both endpoints. Add 1 only for a deliberately inclusive count.
- The number changes later: That is expected for a formula using TODAY. Replace it with a fixed date value if the original result must be preserved.
- The result has decimals: One or both input values probably include a time component. Decide whether to retain elapsed-time precision or remove the time portion from the comparison.
Also check the calculation setting if a workbook seems not to update. With manual calculation enabled, formulas may remain unchanged until recalculation is requested. On Windows, F9 recalculates open workbooks; on Mac, the equivalent shortcut can depend on keyboard settings, and Excel’s Formulas tab provides a Calculate Now command. These recalculate formulas but do not turn TODAY into a continuously updating clock.
Alternative: Count Working Days
Calendar-day subtraction includes Saturdays, Sundays, and holidays. For a work schedule, use NETWORKDAYS, which counts weekdays between two dates and can optionally omit a holiday list. With a start date in A2, today as the end date, and holiday dates (if any) in D2:D10, enter =NETWORKDAYS(A2,TODAY(),D2:D10). The result counts the start and end dates when they are working days, so its convention differs from a simple elapsed-day difference.
If there are no holidays to exclude, use the two-argument form. With the start date in A2, =NETWORKDAYS(A2,TODAY()) counts Monday through Friday in the range. NETWORKDAYS assumes a standard Saturday-Sunday weekend; for a different weekend pattern, Excel provides NETWORKDAYS.INTL. Confirm whether the start date should count and whether your holiday list is complete before using a business-day total for deadlines or service targets.
Frequently Asked Questions
Does TODAY update automatically in Excel?
It updates when Excel recalculates the workbook, including on opening or editing under normal automatic calculation. It does not update continuously every second. Check the workbook’s calculation mode if the displayed date appears stale.
How do I count days until a future date?
With the future date in A2, use =A2-TODAY(). A date three days from today returns 3. The basic reversed subtraction, =TODAY()-A2, returns -3 instead, so choose the order that makes the desired sign clear.
How do I count days between two dates in cells?
Put the start date in A2 and the end date in B2, then subtract A2 from B2 with =B2-A2. A later end date gives a positive elapsed-day count; reversing the dates gives a negative number.
Why does the formula show a decimal?
At least one input likely contains a time as well as a date. Excel stores time as a fraction of a day, so noon contributes one half. If you want calendar-date differences, remove the time portion from the timestamp before subtracting.
Should I add 1 to the result?
Only when your rule counts both the first and last dates. Elapsed time from November 1 to November 3 is 2 days; an inclusive list containing all three calendar dates has 3 days. Pick the convention that matches the task rather than treating either result as universally correct.
Related
- Real Estate Excel Budget Template: Setup and Costs
- Randomize Numbers in Excel Without Duplicates
- Rank in Excel: How to Rank Values Largest to Smallest
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel LEFT Before a Character: Formula and ExamplesTo return the text to the left of a character in Excel, put the text in A2 and…
- Arduino String Functions: Methods, Examples, and TipsArduino string functions are methods for working with text stored in the Arduino String class: for example, length()…
- Excel Function to Keep a Cell Constant in Formulas ExplainedTo keep a cell reference fixed when you copy an Excel formula, make it absolute by adding dollar…
- Using MID in Excel: Extract Text by Position and LengthUse Excel’s MID function to return a chosen number of characters from a text value, starting at a…
- Excel Filter Just for Me: Create a Personal Sheet ViewTo filter a shared Excel worksheet just for yourself, create a Sheet View first, then apply the filter…
- Import CSV Into Excel as Rows, Not Columns the Right WayTo import a CSV into Excel with each record on its own row, open Excel and choose Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.