Count Excel Date-Time Values Between 2 PM and 10 PM
Question details
Count cells containing date-time values when the time of day is between 2:00 PM and 10:00 PM.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains timestamps with both dates and times, and the user needs to count records within a daily time window.
- Observed behavior
- The date component prevents a direct comparison against time-only values, so the time portion must be extracted before counting.
Make sure the cells in A12:A19 contain real Excel date-time serial values rather than text. Confirm whether a timestamp exactly at 10:00 PM should be included or excluded.
Use SUMPRODUCT to count date-time values from 2 PM through 10 PM
Use this formula when timestamps include different dates but you want to count only the time of day from 2:00 PM through 10:00 PM.
MOD(value,1) removes the whole-number date portion and keeps the fractional time portion. SUMPRODUCT then counts values that meet both time conditions.
Click the empty cell where you want the count to appear, such as B1.
Type =SUMPRODUCT((MOD(A12:A19,1)>=TIME(14,0,0))*(MOD(A12:A19,1)<=TIME(22,0,0))) and press Enter.
Replace A12:A19 with the range that contains your date-time values. Keep the same range in both MOD references.

Count Daily Time Windows with WPS Spreadsheet
WPS Spreadsheet supports SUMPRODUCT, MOD, and TIME formulas for counting date-time entries by their time portion. Paste the formula into a result cell, adjust the source range, and calculate the count without removing dates from your data.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheets, and open the workbook containing the date-time column.
- 2. Choose a result cell: Click an empty cell next to or below your timestamp data where the count should be displayed.
- 3. Enter the SUMPRODUCT formula: Enter =SUMPRODUCT((MOD(A12:A19,1)>=TIME(14,0,0))*(MOD(A12:A19,1)<=TIME(22,0,0))) in the formula bar.
- 4. Confirm the result: Press Enter, then edit A12:A19 in the formula if your actual date-time range is different.

Frequently Asked Questions
Why does MOD(A12:A19,1) return the time from a date-time value?
Excel stores dates as whole numbers and times as decimal fractions of a day. MOD(A12:A19,1) returns the fractional portion, which represents the time of day.
How do I exclude timestamps exactly at 10:00 PM?
In the SUMPRODUCT formula, change the ending comparison from <=TIME(22,0,0) to <TIME(22,0,0). This counts times before 10:00 PM only.
Can I use cell references instead of typing 2 PM and 10 PM in the formula?
Yes. Enter the start and end times in cells, such as B1 and B2, then use =SUMPRODUCT((MOD(A12:A19,1)>=B1)*(MOD(A12:A19,1)<=B2)). Format B1 and B2 as Time.
Why does the formula return an error or an unexpected count?
Check that the timestamp cells are valid numeric date-time values, not text. Also verify that the referenced range contains no error values and that both MOD conditions use the same range.




