Excel Date Range Between Two Dates: Formulas and Examples

To calculate the number of days between two dates in Excel, subtract the earlier date from the later one: with the start date in A2 and the end date in B2, enter =B2-A2. Add +1 if you want to count both the start and end dates; use COUNTIFS if you need to count records whose dates fall within a range.

What “between two dates” can mean

An Excel date range can refer to several different tasks, and the right formula depends on what you want to know. You might want the elapsed time between two dates, the number of calendar dates included in a range, a count of dated records that fall within it, or a list containing every date in the interval.

These answers can differ by one day. From January 1 through January 10, the elapsed time is nine days: midnight on January 10 is nine days after midnight on January 1. But the range contains ten calendar dates if you count January 1 and January 10 as included days. Decide which meaning you need before choosing a formula.

Calculate elapsed days

Excel stores valid dates as numbers, so subtracting one date from another returns the difference in days. For example, enter January 1, 2024 in A2 and January 10, 2024 in B2. With those dates in A2 and B2, enter this formula in C2:

=B2-A2

The result is 9. If Excel displays a date instead of the number 9, the calculation may still be correct: the result cell is probably formatted as a date. Select C2, then choose Home > Number > Number Format dropdown > General or Number. You can also use the Number format button in the ribbon’s Number group.

For a range that may be entered in either order, and where you always want a nonnegative elapsed-day count, put the dates in A2 and B2 and use:

=ABS(B2-A2)

With January 1 and January 10 in either order, the result is 9. This is convenient for a simple duration, but it hides which date came first. If reversed dates indicate a data-entry mistake, it is better to flag them rather than silently accept them.

Count both endpoints for an inclusive range

Many schedules, leave requests, rental periods, and date-based reports treat both boundary dates as part of the range. To count January 1 through January 10 as ten calendar dates, add one to the subtraction.

With the start date in A2 and the end date in B2, enter this in C2:

=B2-A2+1

For January 1, 2024 through January 10, 2024, the result is 10. This formula assumes the start date is on or before the end date. If the dates are reversed, the result will not represent a meaningful inclusive count, so consider checking the order first.

Inclusive counting is not automatically “more correct” than subtraction. Use it when your question is how many calendar dates are covered, such as how many dates a campaign runs. Use plain subtraction when you mean elapsed time, such as the number of days between an order and its delivery.

Count dates in a data range

To count records whose dates fall between two boundary dates, use COUNTIFS with a lower-bound and an upper-bound condition. Suppose transaction dates are in A2:A6, the first date to include is in D2, and the last date to include is in E2. If A2:A6 contains January 1, January 5, January 10, January 11, and February 2, and D2 and E2 contain January 1 and January 10, respectively, enter this formula in F2:

=COUNTIFS(A2:A6,">="&D2,A2:A6,"<="&E2)

The result is 3, because January 1, January 5, and January 10 meet both conditions. The comparison operators are joined to the boundary-cell references with &; writing the criteria as text without joining the cell values would not compare against the dates in D2 and E2.

The formula includes both boundary dates. To exclude the final boundary, change the last criterion from "<="&E2 to "<"&E2. To exclude both boundaries, use greater-than and less-than criteria. Keep the same date column in both COUNTIFS criteria ranges, and make sure each criteria range has the same dimensions.

Generate a list of every date

If you need the dates themselves rather than a count, Excel for Microsoft 365 and Excel 2021 can spill a sequence from a single formula. With the start date in A2 and the end date in B2, enter this formula in C2:

=SEQUENCE(B2-A2+1,1,A2,1)

For January 1 through January 10, 2024, the formula returns ten consecutive dates, from January 1 to January 10. Format the spilled cells as dates if Excel shows serial numbers. The formula creates an inclusive list and assumes the end date is not earlier than the start date. If the dates can be reversed, correct their order or use a separate check before generating the sequence.

SEQUENCE is available in Excel 2021 and Microsoft 365, but not in every older Excel release. In an older version, type the first date in a cell, enter the next date one row below using a one-day increment, then fill down until you reach the end date. Check the final row so the list does not extend beyond the intended boundary.

Count working days instead of calendar days

When a range represents business days, NETWORKDAYS counts weekdays from the start date through the end date, including both endpoints. With January 1, 2024 in A2 and January 10, 2024 in B2, enter this in C2:

=NETWORKDAYS(A2,B2)

The result is 8: January 1 to January 5 gives five weekdays, and January 8 to January 10 gives three more. Saturday and Sunday are excluded. This is useful for a basic Monday-to-Friday workweek, but it does not know which dates are public holidays or company holidays.

To exclude holidays, place holiday dates in D2:D3—for example, January 1 and January 8, 2024—and use this formula with the start and end dates still in A2 and B2:

=NETWORKDAYS(A2,B2,D2:D3)

The result is 6, because the two listed holidays are removed from the eight weekdays. The holiday cells must contain real Excel dates, not text that only looks like a date. If an organization uses a different weekend pattern, NETWORKDAYS.INTL provides a weekend argument for customizing which days count as weekends.

Handle times and reversed dates

Excel dates can include times. A date-time value is stored with the date as the whole-number portion and the time as a fraction of a day. As a result, subtracting two date-time values can return a decimal. Format the result as Number to see the fraction, or use a duration format such as [h]:mm when measuring elapsed hours and minutes.

A related issue appears when counting date-time records on the final day. A condition of “less than or equal to January 10” may include midnight at the start of January 10 but miss a record from 3 p.m. that day, because the date-only boundary represents midnight. One reliable approach is to include values from the start date up to, but not including, the day after the end date.

With date-time records in A2:A6, a start date in D2, and an end date in E2, use this formula to count the full end date as well:

=COUNTIFS(A2:A6,">="&D2,A2:A6,"<"&(E2+1))

For example, if the records are January 1 at 9 a.m., January 10 at 3 p.m., January 10 at 11 p.m., January 11 at midnight, and January 12 at noon, with January 1 and January 10 as the bounds, the result is 3. The January 11 midnight record is excluded. The same technique is useful for filtering or summing records with times; use the matching date criteria with a function such as SUMIFS when you need a total rather than a count.

If you want reversed dates to show a warning instead of a negative duration, put the start date in A2 and the end date in B2, then enter this formula in C2:

=IF(B2<A2,"Check dates",B2-A2)

With January 10 in A2 and January 1 in B2, the result is the text “Check dates.” With the dates in the correct order, it returns the elapsed-day count. This makes an input problem visible instead of disguising it with an absolute value.

Check date formatting and common errors

A cell can look like a date without containing a date Excel can calculate with. Dates imported from a text file or copied from a website may be stored as text. If subtraction returns #VALUE!, confirm that both cells contain recognized date values. Re-entering the date, using Text to Columns, or converting the imported data may be necessary depending on its source and format.

Unexpected date display: Change the result cell to General or Number for a day count. For a date list, apply a date format instead.
A result that is one day off: Decide whether you want elapsed days or an inclusive count, then use subtraction or subtraction plus one as appropriate.
A negative result: Check that the start date precedes the end date. Use an IF warning if reversed entries should be corrected.
A missing record on the end date: Check whether the records include times. Use a less-than condition for the day after the end date to include all times on the final day.
Dates treated as text: Confirm the cells are real dates, not text strings. Regional date conventions can affect how imported values are interpreted.

Excel’s default date system on Windows stores dates as serial numbers, which is why arithmetic works. Some workbooks use a different date system, particularly when files have been moved between settings or platforms; dates copied between workbooks using different systems can shift. If dates look consistently offset after a workbook transfer, check the workbook’s date-system setting and compare the original values before changing data.

Choose the right method

Use the question you are answering to select a formula. A useful quick guide is:

For elapsed calendar days, subtract start from end.
For the number of dates including both endpoints, subtract and add one.
For records inside a date interval, use COUNTIFS with lower and upper criteria.
For a spilled list of dates in Excel 2021 or Microsoft 365, use SEQUENCE.
For weekdays, use NETWORKDAYS, optionally supplying a holiday range.

These methods answer different questions, so avoid changing a formula just to make its answer match another method. For instance, a nine-day elapsed difference and a ten-date inclusive count can both be correct for January 1 through January 10.

For formatting, Windows Excel opens the Format Cells dialog with Ctrl+1; on Mac, use Command+1. The dialog can set a result to Number, Date, or a custom format. Formula names and the examples above work in Microsoft 365 for Windows; the core subtraction, COUNTIFS, and NETWORKDAYS functions are also available in current Mac and web versions, although ribbon layouts and keyboard shortcuts can differ. SEQUENCE availability depends on the Excel version, not on whether the computer is a Mac or Windows PC.

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 elapsed days; add one if both dates should count.

Does Excel count the start and end dates?
Simple subtraction does not count the start date as a full included date. It returns the elapsed difference. For an inclusive calendar-date count, use the end date minus the start date plus one.

How do I count dates between two dates in a column?
Use COUNTIFS with a greater-than-or-equal-to condition for the start date and a less-than-or-equal-to condition for the end date. If the cells contain times, use a less-than condition for the day after the end date.

How do I count weekdays between two dates?
Use NETWORKDAYS with the start and end dates. It includes weekdays at both ends and excludes Saturday and Sunday by default. Add a holiday-date range if those dates should also be excluded.

Why does my date subtraction show a date instead of a number?
The result cell is likely formatted as a date. Change its format to General or Number to display the day count. If the result is an error instead, check whether the inputs are actual Excel dates rather than text.

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.