Function Problems

Fix COUNTIFS Missing Dates with Times in Excel

Olivia Miller Oct 12, 2026 870 views

Question details

The user needs to accurately count records between two dates using COUNTIFS when the data cells contain both date and time values.

How to Fix COUNTIFS Missing Dates with Times in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Counting data records within a specific date range where timestamps are present in the date cells.
Observed behavior: The COUNTIFS function excludes records occurring on the end date because a date without a time defaults to midnight (00:00:00), ignoring later times on that same day.

Before you start

Ensure your date column is formatted correctly as 'Date' or 'Custom' (displaying both date and time) rather than as plain text to allow the formula to calculate values accurately.

Solution 1Recommended

Use a Less-Than Criterion with the Next Day

This is the most reliable method. By adding 1 to your end date and using a strictly less-than operator, you include all times up to 11:59:59 PM of your target date.

Because Excel stores dates as integers and times as decimals, entering '9/30/2023' equates to exactly midnight. By setting your upper boundary to '<10/1/2023', you capture every possible time on September 30.

Identify your data and criteria

Locate the range containing your timestamps (e.g., A18:A2000), your start date cell (e.g., A5), and your end date cell (e.g., B5).

Select the result cell

Click on the cell where you want the total count to appear.

Enter the adjusted COUNTIFS formula

Type the formula =COUNTIFS(A$18:$A$2000, ">="&A5, A$18:$A$2000, "<"&B5+1).

Execute the function

Press Enter. The formula now effectively captures all times on the end date by setting the upper bound to midnight of the following day.

Use a Less-Than Criterion with the Next Day

Best Practice: Using the '<' operator with 'B5+1' is much safer than trying to manually add 23:59:59, as it accounts for all exact millisecond values immediately before midnight.

Solution 2

Append End-of-Day Time to the Criterion

You can modify the end date condition to explicitly include the time 23:59:59, forcing Excel to read the entire day.

Efficient Spreadsheet Management

Master Complex Formulas with WPS Spreadsheet

WPS Spreadsheet offers full compatibility with standard Excel functions, including COUNTIFS. You can easily manage dates, times, and complex data analysis tasks in a lightweight, user-friendly environment without worrying about formula syntax errors.

1. Download and Install: Download WPS Office for free and open WPS Spreadsheet.
2. Open Your Data File: Open your existing .xlsx file containing the date and time records.
3. Apply the Formula: Select the target cell and type your COUNTIFS formula, using the '<' and '+1' method for accurate date range counting.
4. Calculate Results: Press Enter to instantly calculate your records without missing any end-of-day timestamps.

Fully compatible with Microsoft Excel formulas and .xlsx formats.Advanced date and time formatting tools for accurate data analysis.Lightweight software that processes large datasets quickly and without lag.Free to use with an intuitive, tabbed user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel treat a plain date as midnight?

In spreadsheet programs, dates and times are stored as serial numbers. A whole number represents a specific date, and any fractional part represents the time of day. A date without a time is exactly a whole number, mathematically equating to 00:00:00 (midnight). Therefore, any time later in the day is technically a larger number.

Can I use the INT function to fix this issue?

Yes. You can use a helper column with the INT function to strip the time from your timestamps (e.g., =INT(A2)), leaving just the whole number date. Then you can use a standard COUNTIFS formula on that new helper column without needing to adjust your end date.

Does this date-time issue affect SUMIFS and AVERAGEIFS?

Yes, any function relying on logical comparisons for date ranges (like SUMIFS, AVERAGEIFS, and MAXIFS) will experience this exact same behavior if the referenced data contains underlying time values. The +1 day workaround with the less-than operator applies to all of these functions.

Excel & Data Problems