Function Problems

Count Excel Date-Time Values Between 2 PM and 10 PM

Muhammad TalhaMuhammad Talha Nov 12, 2026 868 views

Question details

Count cells containing date-time values when the time of day is between 2:00 PM and 10:00 PM.

Count Excel Date-Time Values Between 2 PM and 10 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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

Click the empty cell where you want the count to appear, such as B1.

2
Enter the counting formula

Type =SUMPRODUCT((MOD(A12:A19,1)>=TIME(14,0,0))*(MOD(A12:A19,1)<=TIME(22,0,0))) and press Enter.

3
Adjust the timestamp range if needed

Replace A12:A19 with the range that contains your date-time values. Keep the same range in both MOD references.

Use SUMPRODUCT to count date-time values from 2 PM through 10 PM
10 PM is included: The <=TIME(22,0,0) condition includes timestamps at exactly 10:00 PM. Change <= to < if 10:00 PM must be excluded.
Work with date-time formulas in WPS Spreadsheet

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. 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheets, and open the workbook containing the date-time column.
  2. 2. Choose a result cell: Click an empty cell next to or below your timestamp data where the count should be displayed.
  3. 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. 4. Confirm the result: Press Enter, then edit A12:A19 in the formula if your actual date-time range is different.
Use SUMPRODUCT and MOD to count timestamps within a selected time range.Keep the original date and time values intact while evaluating time only.Open and edit Microsoft Excel .xlsx and .xls workbooks with formula compatibility.Use a familiar spreadsheet interface for quick formula entry and analysis.
microsoft office alternative - wps office

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.