Excel Day Counter: Count Days Between Dates

With a start date in A2 and an end date in B2, enter =B2-A2 to count the days between them. For example, if A2 is January 1, 2025, and B2 is January 10, 2025, the result is 9 days; add 1 if you want to count both dates.

How a basic Excel day counter works

Excel stores recognized dates as serial numbers, so subtracting one date from another gives the number of days separating them. The start date is not counted as a full day in the basic calculation: January 1 to January 10 spans nine 24-hour date intervals, even though the calendar shows ten dates when both endpoints are included.

To set up a reusable counter, put a start date in A2 and an end date in B2, then place the subtraction formula in C2. Format the result cell as a number if it appears as a date. In Microsoft 365 for Windows, select the result cell and choose Home > Number Format > Number. You can also open Format Cells with Ctrl+1; on Mac, use Command+1. A zero-decimal number format is usually easiest to read.

Once the first row works, copy the formula down to count days for other pairs of dates. Excel adjusts the references in each copied row. If you instead need a fixed reference, use dollar signs in the reference, such as $A$2, but a straightforward day counter usually needs ordinary relative references.

Choose elapsed days or an inclusive count

The right formula depends on what you mean by “days.” An elapsed-day count measures the interval between dates. An inclusive calendar count includes both the first and last dates, which is common for booking lengths, event schedules, and date ranges described as “January 1 through January 10.”

With the start date in A2 and the end date in B2, use =B2-A2+1 for an inclusive count. For January 1 through January 10, 2025, the result is 10: the nine-day difference plus one for the starting date. This does not mean that a nine-night stay lasts ten nights; lodging is usually counted by elapsed nights, so a check-in date of January 1 and checkout date of January 10 normally represent nine nights.

Be consistent when communicating the result. If a report says “days between,” readers may expect elapsed days. If it says “days included” or “calendar days in the period,” an inclusive count may be clearer. A one-day period illustrates the distinction: the same start and end date gives zero elapsed days, but one inclusive calendar day.

Use DATEDIF when a named day function helps

Excel also supports DATEDIF, a compatibility function that returns the difference between two dates in chosen units. For days, its unit argument is "d". With a start date in A2 and an end date in B2, use =DATEDIF(A2,B2,"d"); for January 1 and January 10, 2025, it returns 9.

For a simple day count, subtraction is often more transparent and easier to adapt. DATEDIF can be handy when a workbook already uses it or when a formula also calculates completed months or years. It is not listed in Excel’s standard function autocomplete in the same way as many newer functions, but it is supported in Excel. Like subtraction, it expects the earlier date first; a start date later than the end date can produce an error. Check the order of your dates if that happens.

Count weekdays and exclude holidays

Calendar-day subtraction counts Saturdays, Sundays, and holidays. For a standard Monday-to-Friday workweek, use NETWORKDAYS when you need the number of working days in a date range. It counts both the start and end dates when they are workdays, unlike a simple subtraction formula’s elapsed-interval convention.

With the start date in A2, the end date in B2, and a holiday date in E2, enter =NETWORKDAYS(A2,B2,$E$2:$E$2). If A2 is Monday, January 6, 2025, B2 is Friday, January 10, and E2 contains Wednesday, January 8, the result is 4: five weekdays in the range minus one holiday. Put additional holiday dates in a larger range, such as E2:E10, and use that range in the formula. Empty cells in a holiday range are ignored.

NETWORKDAYS assumes Saturday and Sunday are the weekend. For a different weekend pattern, use NETWORKDAYS.INTL. With the same date layout and holiday list, =NETWORKDAYS.INTL(A2,B2,1,$E$2:$E$2) returns 4 in the example above; the weekend argument 1 specifies Saturday and Sunday. The function also supports a seven-character weekend pattern for less common schedules, so check Excel’s function help before entering a custom pattern.

Make the counter update with today’s date

To count from a fixed start date to the current date, Excel’s TODAY function supplies today’s date and updates when the workbook recalculates. With the starting date in A2, enter =TODAY()-A2. If A2 is November 1, 2026, and Excel recalculates the workbook on November 2, 2026, the result is 1. The value changes over time; it is not a permanent record of the count on the day you first entered the formula.

For an inclusive count through today, with the same start date in A2, use =TODAY()-A2+1. For a live weekday count, with the start date in A2 and holidays in E2:E10, use =NETWORKDAYS(A2,TODAY(),$E$2:$E$10). The latter counts today if it is a weekday and not listed as a holiday. TODAY does not include a time of day; use NOW only when a calculation needs the current date and time, and account for fractional days in the result.

Understand dates, times, and number formats

A date entered and recognized by Excel behaves differently from text that merely looks like a date. If subtraction returns #VALUE!, check whether either cell contains text, spaces, or an imported value Excel did not parse as a date. Re-entering the date in a recognized format or converting the imported data may resolve it. Changing a cell’s display format alone does not turn text into a real date.

Excel dates can be displayed in many ways without changing their underlying value. A result such as 9 might display as a date if the cell is formatted as Date; switch the result to General or Number. Conversely, if a date serial appears instead of a readable date, apply a date format to the date cells. The display format changes presentation, not the arithmetic.

Cells can also contain a date and a time. Subtracting date-times returns a fraction of a day: for example, half a day is 0.5. If the output is formatted as a whole number, the fraction may be hidden, which can make a duration seem surprising. Decide whether you need whole calendar-date differences, exact elapsed time, or rounded days before choosing a format or calculation. A date-only counter is clearest when the source cells contain dates without times.

Excel workbooks can use either the 1900 or 1904 date system. The systems assign different serial values to the same modern date, so copying raw date values between workbooks with different systems can shift displayed dates by 1,462 days. Ordinary subtraction within one workbook remains consistent. This is mainly a consideration when moving data between workbooks or systems, not a reason to change the date system for a routine counter.

Fix common day-counter problems

The result is a date instead of a number: change the result cell’s format to General or Number.
The result is negative: verify that the end date is later than the start date. A negative value is mathematically valid when the dates are reversed, but may indicate swapped inputs.
The formula shows in the cell instead of calculating: check that the entry starts with an equals sign and that the cell is not formatted as Text. Change the format, then re-enter the formula. Also check whether Show Formulas is enabled.
The count is unexpectedly one day short or long: determine whether the task needs elapsed days or an inclusive count. Add one only for the latter.
A date range includes weekends or a holiday: use NETWORKDAYS for standard workdays and supply a holiday range, rather than subtracting dates.
The answer is a decimal: one or both inputs probably include times. Decide whether those times are meaningful before rounding or hiding the fraction with a number format.

For date entry, prefer unambiguous values such as a date selected from a calendar or a format consistent with your regional settings. Excel can interpret slash-separated dates differently across locales. If a workbook is shared internationally, a clear displayed format such as 6-Jan-2025 can reduce confusion, although formatting alone does not change the stored date.

Pick the right method for the task

A quick choice prevents most counting mistakes:

Elapsed calendar days: subtract the start date from the end date.
Both endpoints included: subtract and add one.
Completed workdays, Monday through Friday: use NETWORKDAYS and provide holidays when relevant.
Workdays with a nonstandard weekend: use NETWORKDAYS.INTL and set the weekend pattern.
Days from a date through today: use TODAY as the changing end date.

For a small table, a formula is more reliable than manually counting dates and easier to update when an input changes. For a one-off check, a date calculator may be faster, but it will not recalculate alongside dates in a workbook. In a shared spreadsheet, label columns clearly—for example, “Elapsed days” or “Workdays, excluding listed holidays”—so another reader can tell what the number means.

Frequently Asked Questions

Does Excel count the start date in a day counter? Simple date subtraction returns elapsed days and does not count the start date as an additional day. Add 1 when the requirement is to include both endpoints, such as counting calendar dates in a range.

How do I count days from a date until today? Put the start date in A2 and use TODAY as the end date in a subtraction formula. The result updates when Excel recalculates; use an inclusive count instead if the starting date should count as day one.

Can Excel count only business days? Yes. NETWORKDAYS counts Monday through Friday and can exclude dates listed as holidays. NETWORKDAYS.INTL lets you specify a different weekend pattern.

Why does Excel show a date instead of the number of days? The result cell is likely formatted as a date. Change its format to General or Number; that changes how the result appears, not the calculation.

Why is my day count a decimal? One or both input cells may include a time as well as a date. Excel represents the time as a fraction of a day, so subtracting date-times can produce a decimal result.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.