Dates & regional formats · 5 min read

Excel SUMIFS Date Range: Include the Whole End Date, Even with Times

A September report can miss transactions from the afternoon of September 30 when its end condition means midnight at the start of that date. The cells may display only dates, hiding the reason for the short total. Use an explicit start and an exclusive next-day boundary so the report includes the entire intended calendar period.

JHSOFT editorialUpdated September 13, 2026

WORKED EXAMPLESynthetic data

timestamp,category,amount
2026-08-31 23:59:00,Parts,5
2026-09-01 00:00:00,Parts,10
2026-09-15 12:30:00,Parts,20
2026-09-30 00:00:00,Parts,30
2026-09-30 18:45:00,Parts,40
2026-10-01 00:00:00,Parts,50
2026-09-20 09:00:00,Service,60

September 1 through September 30 totals 160 across categories, including the September 30 evening record. Restricting category to Parts gives 100. A Parts formula ending at <=September 30 midnight gives only 60.

Download sumifs-dates-input.csv ↓Download sumifs-dates-expected.csv ↓Download sumifs-dates-setup.txt ↓Save this formula →Explore the practice pack →

Create dates and times that Excel can compare numerically

The SUMIFS approach works in Excel 2016 and later desktop editions. Create headers timestamp, category, and amount in A1:C1 and enter the seven records through row 8. The CSV contains ISO-style labels for reading, but a CSV import does not guarantee numeric Excel dates. Use the downloadable setup formulas to build A2:A8 with DATE and TIME for this exercise.

Format A2:A8 as yyyy-mm-dd hh:mm:ss so the hidden time portion is visible. Enter =DATE(2026,9,1) in E2 and =DATE(2026,9,30) in F2. Put Parts in H2. These parameter cells represent whole calendar dates, not an arbitrary start time and end time. Validate those inputs before evaluating a total. Check that A2:A8 and C2:C8 are numeric, E2/F2 are whole dates, and the start is not later than the end.

Example timestamp construction

A2: =DATE(2026,8,31)+TIME(23,59,0)
A3: =DATE(2026,9,1)+TIME(0,0,0)
A6: =DATE(2026,9,30)+TIME(18,45,0)
E2: =DATE(2026,9,1)
F2: =DATE(2026,9,30)
H2: Parts

Sum from the start date up to the next day after the end

Enter the main formula in E5. The lower condition admits timestamps at or after the beginning of September 1. The upper condition admits timestamps strictly before October 1 begins. That interval contains both midnight and evening entries on September 30 while excluding the record exactly at October 1 midnight.

Trace the records instead of relying only on the final number. The included amounts are 10, 20, 30, 40, and 60, totaling 160. The August 31 amount of 5 lies before the interval and the October 1 amount of 50 lies on the excluded boundary. This deliberate pair of outside records checks that the formula is neither too broad nor too narrow.

E5: all categories in the date interval

=SUMIFS(C2:C8,A2:A8,">="&E2,A2:A8,"<"&(F2+1))

The September 30 evening transaction is inside the interval, while October 1 midnight is outside. — Illustrated example. A half-open interval includes the start boundary and excludes the following day.

Add a category while preserving the same date boundaries

Enter the category formula in E6. SUMIFS receives the amount range first, followed by matching range-and-condition pairs. Every range here covers the same seven records, starting at row 2 and ending at row 8. The category condition removes the Service amount of 60 while leaving the four September Parts records.

The expected Parts result is therefore 100. A useful reconciliation is 160 minus 60 equals 100. If the category formula gives 160, inspect whether H2 really supplies the intended category condition. If it gives 0, inspect the category labels and their spaces before assuming the date logic has failed. The sample category is the literal label Parts. SUMIFS criteria can interpret * and ? as wildcards; review or escape those characters when they belong to a category name.

E6: category in H2 within the same interval

=SUMIFS(C2:C8,A2:A8,">="&E2,A2:A8,"<"&(F2+1),B2:B8,H2)

Explain the tempting formula that misses the evening record

For comparison only, enter the formula below in E7. Its end condition includes timestamps no later than the numeric value in F2. Because F2 represents midnight starting September 30, the amount of 40 at 18:45 is left out. The three admitted Parts amounts are 10, 20, and 30, producing 60.

Changing the cell display to show only a date does not remove that afternoon timestamp. Likewise, choosing an arbitrary time such as 23:59 as the last allowed moment can exclude later seconds. The next-day boundary makes the calendar intent clearer and avoids having to guess the finest time precision present in the export.

E7: intentionally incomplete end-date comparison

=SUMIFS(C2:C8,A2:A8,">="&E2,A2:A8,"<="&F2,B2:B8,H2)

An inclusive midnight boundary loses the evening amount even when both source cells display September 30. — Illustrated example. This illustration isolates the two September 30 Parts transactions.

Reject reversed or time-bearing report parameters

The main formula assumes E2 is not later than F2 and both cells have no time fraction. Keep that contract visible near your report inputs. If someone types September 30 at noon as F2, adding 1 extends the window to noon on October 1, which is not the full-day report described here. The right response is to correct or explicitly normalize the input rule.

For a report whose parameters are genuine timestamps rather than dates, choose an exact interval such as start-inclusive and end-exclusive, and compare directly to the end timestamp without adding a day. Do not reuse a calendar-date shortcut for a shift log without reviewing the definition. Also establish the time zone before importing timestamps from more than one system; these formulas perform no time-zone conversion.

Use boundary cases as a reusable report check

Keep the seven-row fixture beside your report documentation. It includes the previous day, the exact start, a middle timestamp, two end-date times, the exact next-day boundary, and an alternate category. When adjusting source ranges or importing from a new system, rerun this small fixture first and compare E5, E6, and E7 to 160, 100, and 60.

The seven-row fixture was checked with independent date arithmetic and recalculated in desktop Excel 16.0, build 5569.0, on September 13, 2026. The three formulas returned 160, 100 and 60. The native test constructed numeric timestamps with DATE and TIME; it did not verify a CSV import dialog or every Excel locale. Confirm your imported timestamps and amounts are numeric before adapting the report.

Common questions

Should I use <= end date or < end date + 1?

For an inclusive calendar end date and data that may contain times, use < end date + 1 with date-only parameters. For a precise timestamp end boundary, define the interval explicitly and do not add a day automatically.

Why does my September total omit transactions from September 30?

Your end-date cell may mean midnight starting that day. An evening timestamp is later even if formatting hides its time. Display the times and test the next-day exclusive boundary.

Does formatting text as a date make it a numeric date?

No. Formatting controls display. Build the sample with DATE and TIME or import with an explicit type, then verify that the values are numeric before using date comparisons.

Sources & method

Examples and diagrams use synthetic data. Application instructions follow the linked documentation; available menus and options can vary. Our sample checks do not establish behavior in every Excel or Google Sheets version. Read our AI-assisted editorial method.

Browse all field guides →