Excel Formula Between Two Dates: Count, Sum, or Check

If dates are in A2:A100, the start date is in D1, and the end date is in E1, count dates within that range, including both endpoints, with =COUNTIFS(A2:A100,">="&D1,A2:A100,"<="&E1). For other needs, Excel can also sum values in a date range, check whether one date falls inside it, or calculate the number of days between the endpoints.

What “between two dates” can mean

The right formula depends on what you want Excel to do with the dates. A common task is counting records whose dates fall inside a period, such as the number of orders placed in March. Another is adding amounts associated with those records, such as monthly sales. You might instead need a yes-or-no result for one date, or the duration between a start and end date.

These tasks use related but different formulas. COUNTIFS counts cells that meet date conditions, SUMIFS adds corresponding values, and comparisons such as greater-than-or-equal-to test a single date. Subtracting one date from another returns elapsed days. Choosing the right operation first helps avoid a formula that looks plausible but answers a different question.

In the examples below, dates are stored in cells rather than typed into formula criteria. That makes it easier to change the period without rewriting the formula. The examples assume Microsoft Excel for Microsoft 365 on Windows and use commas between function arguments.

Count dates inclusively with COUNTIFS

Use COUNTIFS when you need to count dates on or after a start date and on or before an end date. With dates in A2:A100, the start in D1, and the end in E1, the formula is:

=COUNTIFS(A2:A100,">="&D1,A2:A100,"<="&E1)

The formula applies two conditions to the same date range. The first criterion, ">="&D1, includes dates equal to or later than the start. The second, "<="&E1, includes dates equal to or earlier than the end. The ampersand joins each comparison operator to the date value in its cell; it does not add dates together.

For example, suppose A2:A6 contains January 1, January 5, January 10, January 20, and February 1, 2025, while D1 is January 5 and E1 is January 20. The result is 3: the January 5, January 10, and January 20 entries qualify. Both boundary dates count. If the start date is later than the end date, the formula normally returns 0, so check that the two inputs are in the intended order.

To count dates strictly after the start and strictly before the end, change the operators to ">" and "<". Make that choice deliberately: “between” often means inclusive in ordinary reporting, but some business rules define a period differently.

Sum amounts recorded during a date range

When each date has a corresponding amount, use SUMIFS. Assume transaction dates are in A2:A100, amounts are in B2:B100, the start date is in D1, and the end date is in E1. To add amounts from the inclusive range, enter:

=SUMIFS(B2:B100,A2:A100,">="&D1,A2:A100,"<="&E1)

Suppose the three qualifying records in the earlier example have amounts of 12, 25, and 8. The result is 45. In SUMIFS, the first argument is the sum range (B2:B100); the following pairs specify a criteria range and a condition. The date range appears twice because it must satisfy both the lower and upper boundary.

The sum range and each criteria range should cover corresponding rows and have the same dimensions. If the dates are in A2:A100 but the amounts are accidentally referenced as B2:B99, Excel may return an error rather than the intended total. Also confirm that amounts are numeric values, not numbers stored as text; text entries are not added as ordinary numbers.

Check whether one date falls inside the range

For a single date in A2, with the start date in D1 and end date in E1, return a label using:

=IF(AND(A2>=$D$1,A2<=$E$1),"Within","Outside")

If A2 contains January 10, 2025, and D1 and E1 contain January 5 and January 20, 2025, the result is “Within.” The dollar signs keep the boundary references fixed if you copy the formula down a column, while A2 changes to A3, A4, and so on. AND is true only when both comparisons are true; IF displays the chosen text for each outcome.

A blank A2 is treated like a zero in these comparisons and will generally return “Outside,” but it is clearer to handle missing dates explicitly if blanks are possible. For example, a formula can first test whether A2 is empty and return an empty string, then perform the date check. If you use such a version, remember that a formula returning an empty string is not the same as a genuinely empty cell for every Excel operation.

Calculate elapsed days or inclusive calendar days

Excel stores valid dates as serial numbers, so subtracting the earlier date from the later one gives the number of elapsed days. With a start date in D1 and an end date in E1, use:

=E1-D1

For January 1 through January 31, 2025, this returns 30. That is the elapsed time from the start of January 1 to the start of January 31, not the count of calendar dates touched. If you want to count both endpoint dates as whole calendar days, use =E1-D1+1 with the same D1 and E1 layout; the result is 31.

This distinction matters in reports, leave calculations, and event schedules. A stay beginning on Monday and ending on Tuesday spans one elapsed day, but includes two named calendar dates if both the arrival and departure dates are counted. The formula should reflect the rule you are reporting, not just the wording “number of days.”

Handle dates that include times

A cell can contain both a date and a time, even if its number format displays only the date. For example, a record stored as January 31, 2025 at 3:00 PM is later than January 31, 2025 at midnight. Consequently, a criterion of less than or equal to an end date entered as January 31 may exclude records later that day.

To include every time on the end date, use a boundary that is exclusive of midnight on the following day. With date-and-time values in A2:A100, a start date in D1, and an end date in E1, count the inclusive calendar period with:

=COUNTIFS(A2:A100,">="&D1,A2:A100,"<"&(E1+1))

If D1 is January 5 and E1 is January 20, this includes timestamps from January 5 at midnight up to, but not including, January 21 at midnight. The same approach works for summing values: keep the same two date criteria and use the amount range as the first argument of SUMIFS. This is generally more reliable than trying to specify the final instant of the day, since timestamps can have different time precision.

Count working days instead of calendar days

If the question concerns weekdays rather than all calendar days, NETWORKDAYS counts workdays between two dates and includes both endpoints when they are working days. With the start date in D1 and the end date in E1, use:

=NETWORKDAYS(D1,E1)

For Friday, January 3, 2025 through Tuesday, January 7, 2025, the result is 3: Friday, Monday, and Tuesday. Saturday and Sunday are excluded. You can also supply a range of holiday dates as an optional third argument; for example, with holidays listed in H2:H10 and the same D1 and E1 inputs, use =NETWORKDAYS(D1,E1,H2:H10). A holiday is excluded from the count even if it falls on a weekday.

By default, this function treats Saturday and Sunday as the weekend. For a different weekend pattern, Excel provides NETWORKDAYS.INTL, which accepts a weekend setting. Verify the weekend pattern and holiday list against the schedule you need to report; neither function can infer an employer’s or organization’s calendar automatically.

Make sure Excel recognizes the values as dates

Excel date formulas work best when the cells contain real date values, not text that merely looks like a date. A date can be displayed as “Jan 5” or “1/5/2025” while retaining the same underlying value; changing the cell’s number format changes how it looks, not the date itself. Text imported from another system may not convert just because it has a familiar appearance.

If a formula returns 0 unexpectedly, check the source cells and the criteria cells. Select a date cell and try changing its number format to a date or to General; a recognized date will display as a serial number under General. Also check whether day and month order in imported text matches your regional settings. Ambiguous entries such as 03/04/2025 can be interpreted differently in different locales.

For reliable date entry, use Excel’s date controls or a formula such as =DATE(2025,1,5) in a cell when creating a fixed example. The DATE function constructs a date from year, month, and day arguments, avoiding some ambiguity in typed text. When dates come from a file, use an appropriate import or conversion process and verify a few converted values before relying on a summary.

Common formula problems and fixes

Criteria operators are missing or misplaced. For a cell reference, combine the operator and value, as in ">="&D1. A bare text criterion such as ">=D1" compares against the literal characters D1 rather than the cell’s date.
The endpoint rule is wrong. Use >= and <= for inclusive dates. Use strict comparisons only when the start and end themselves should be excluded.
The end-date timestamps are omitted. If the source includes times, use a less-than condition for the day after the end date, as shown above.
Dates are text or contain unexpected spaces. Convert or clean imported data, then confirm Excel recognizes the result as a date. Formatting text as a date alone does not reliably convert it.
The ranges do not line up. In a sum formula, the date criteria ranges and amount range should cover the same records and matching row spans.
The cells look like dates but use different years. A display format may hide the year. Show the year while checking the start, end, and source dates.

When a result seems wrong, inspect a few source rows that should qualify and a few that should not. Comparing their actual values to the boundary cells often reveals whether the issue is inclusivity, hidden times, text conversion, or simply an incorrect range reference.

Frequently Asked Questions

What is the Excel formula to count dates between two dates?
Use =COUNTIFS(A2:A100,">="&D1,A2:A100,"<="&E1) when dates are in A2:A100, the start date is in D1, and the end date is in E1. It counts both endpoints.

How do I count dates including the end date when cells contain times?
Use a less-than condition against the day after the end date: =COUNTIFS(A2:A100,">="&D1,A2:A100,"<"&(E1+1)). This includes all times on the date in E1.

How do I sum values between two dates?
Use SUMIFS with the amount column as the sum range and the date column as both criteria ranges. For example, if amounts are in B2:B100 and dates in A2:A100, apply the start and end criteria to A2:A100.

Does Excel include the start and end dates in a date range?
It depends on the comparison operators. The common inclusive test uses >= for the start and <= for the end. Strict > and < comparisons exclude the boundary dates.

Why does subtracting two dates give one day less than expected?
Subtraction returns elapsed days. To count both calendar dates in a range, add 1, provided that inclusive counting matches the rule you need.

Can I count weekdays between two dates?
Yes. Use NETWORKDAYS for a Monday-to-Friday workweek, optionally providing a holiday range. Use NETWORKDAYS.INTL when the weekend pattern differs from Saturday and Sunday.

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.