WPS Spreadsheets COUNTIF: Exclude Dates with Times from Results
Question details
The user needs to count records after a specific date without including date-time values from that date.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A COUNTIF formula uses a criterion such as ">06/30/2023" to count dates after June 30, 2023.
- Observed behavior
- Date-time values on June 30 are included because they are later than midnight on June 30.
Confirm that the values in column B are recognized as dates or date-time values rather than text. Identify the first date that should be included in the result; for this example, it is July 1, 2023.
Count from the next calendar day with COUNTIF and DATE
Use this solution when the entire cutoff date must be excluded, including all times recorded during that day.
A date-time value on June 30 is numerically greater than midnight at the start of June 30, so a criterion such as ">06/30/2023" can include it. Set the threshold to the next day with DATE to exclude the complete date.
In WPS Spreadsheets, click the cell where you want the count to appear.
Enter =COUNTIF($B:$B,">="&DATE(2023,7,1)) and press Enter. This counts values in column B from July 1, 2023 onward.
Review the values in column B and confirm that all date-time entries from June 30 are excluded while July 1 and later entries are included.
Use WPS Spreadsheets to handle COUNTIF date-time criteria
WPS Spreadsheets lets you enter and evaluate the COUNTIF formula directly, using DATE to create a reliable next-day cutoff and exclude all time values from the previous date.
- 1. Open the spreadsheet: Launch WPS Office, choose Spreadsheets, and open the workbook containing the date-time records.
- 2. Choose the output cell: Click an empty cell where the COUNTIF result should be displayed.
- 3. Enter the cutoff formula: Type =COUNTIF($B:$B,">="&DATE(2023,7,1)) and press Enter.
- 4. Verify the result: Check that entries dated June 30 at any time are excluded and entries from July 1 onward are counted.
Frequently Asked Questions
Why does COUNTIF with ">06/30/2023" include June 30 time values?
The criterion is compared with midnight at the start of June 30. Any June 30 value with a time, such as 06/30/2023 15:00, is later than that midnight value and can be counted.
What formula excludes all date-time values on June 30?
Use =COUNTIF($B:$B,">="&DATE(2023,7,1)). The threshold begins at midnight on July 1, so every value from June 30 is excluded.
Can I use a cell instead of DATE in the COUNTIF formula?
Yes. If cell D1 contains July 1, 2023 as a valid date, use =COUNTIF($B:$B,">="&D1). Make sure D1 is stored as a date, not text.
Does this formula work with times stored in the date column?
Yes, provided the date-time entries are recognized as numeric spreadsheet dates. The next-day threshold compares both the date and time portions correctly.
