COUNTIFS between dates: count a full month, including timestamps

Define the reporting question first

A repair desk keeps one row per ticket. We want tickets received during March 2026 whose current status is Open. This is not the number that were open at the end of March: that historical question would require status history or a closed-at date.

Enter the ticket IDs in column A, real date-time values in B and statuses in C. The following list deliberately includes records just outside the month.

Count from the first day up to the next month

Use >= for the start and < for the first instant of April. Repeat the same date range for these two conditions, then add the status condition. Every test must pass on the same row.

=COUNTIFS(B2:B6,">="&DATE(2026,3,1),B2:B6,"<"&DATE(2026,4,1),C2:C6,"Open")

The answer is two tickets

T-72 is exactly on the start boundary and qualifies. T-74 also qualifies: 18:00 on 31 March is still before 1 April. T-71 is too early, T-75 is exactly on the excluded end boundary, and T-73 has the wrong status.

Remove the status condition and three tickets fall inside March. That cross-check helps separate a date-range problem from a status-label problem.

Why an end date of 31 March can miss records

In a spreadsheet date-time value, a whole number represents the date and the fractional part represents the time. DATE(2026,3,31) is the start of that day. A condition of <=DATE(2026,3,31) excludes T-74 because its time is later than midnight. In this example that incorrect boundary returns 1 instead of 2.

Using <DATE(2026,4,1) includes the whole final day without guessing its last possible second. The same pattern works for adjacent reporting periods without counting the boundary record twice.

Make the period selectable

Put the start date in E2 and the first date after the period in F2. For this example use =DATE(2026,3,1) in E2 and =DATE(2026,4,1) in F2. The ampersand joins each comparison operator to the numeric value of its boundary cell.

To report April, change E2 to 1 April and F2 to 1 May. The original sample then returns 1, for T-75. Keep the year explicit so a saved example does not silently change with today’s date.

=COUNTIFS(B2:B6,">="&E2,B2:B6,"<"&F2,C2:C6,"Open")

Check the stored dates, not just their appearance

For a reproducible exercise, the practice table below uses numeric values from the 1900 date system. The five received values are 46081, 46082, 46096, 46112.75 and 46113. The .75 in the fourth value means 18 hours out of 24. E2 is 46082 and F2 is 46113. In a workbook, date-time formatting makes these values easier to read.

Do not paste the displayed date labels from this article as unverified text and assume that changing the cell format converts them into dates. Check imported timestamps and use one agreed timezone before reporting. This example does not perform timezone conversion.

Keep both date ranges and the status range aligned. If your export contains multiple rows per ticket, COUNTIFS counts rows; it does not deduplicate ticket IDs.

Test the boundaries yourself

Change B5 from 46112.75 to 46113. T-74 moves to the start of April and the March count falls from 2 to 1. Restore B5, then change C4 from Closed to Open. The count rises to 3. These changes test the date boundary and status condition separately.

Further reading

• Microsoft: count numbers or dates based on a condition
• Microsoft: COUNTIFS function