digestVidSign in
summary

excelisfun - Excel Magic Trick 1163: COUNTIFS For Dates, When Dates Have Time Value Too? COUNTIF too.

Thumbnail of the video “excelisfun - Excel Magic Trick 1163: COUNTIFS For Dates, When Dates Have Time Value Too? COUNTIF too.”
Community upload. AI output has not been independently verified.
Author: excelisfun
Categories: Science Technology
Tags: science, technology
Views: 2 · Last viewed: 2026-10-16 00:53 UTC

Short Summary

Main recommendation: To count dates that have time values in Excel, use a helper column with INT to strip the time portion before applying COUNTIFS, or use a single-cell array formula with SUMPRODUCT, INT, and a double negative when you want to avoid a helper column. Plain COUNTIFS on a date criterion returns zero because the cells store serial numbers with decimal time parts that don't match the integer date.

Key Points

  • Dates in Excel are serial numbers (days since December 31, 1899), and times are decimals between 0 and 1, so a timestamped date is not equal to the plain date criterion.
  • Removing number formatting (Home > Number > General, or Ctrl+Shift+~) reveals the underlying serial number and decimal so you can see the time component.
  • The INT function returns only the integer (whole-number) part, which is the date without the time.
  • A helper column of INT results makes COUNTIFS work directly, because the criterion then matches the integer date.
  • Without a helper column, wrapping INT over the whole range inside SUMPRODUCT simulates the helper column in one cell.

Details Worth Noting

  • Concrete instructions:
    • Helper column method: In a new column, enter =INT(cell) for the first date, format it as Short Date, fill it down, then use =COUNTIFS(helper_range, 1/20/2014) with the range locked (Ctrl+Shift+Down, then F4) and the criterion set to the date. Copy the formula and paste it down the adjacent cells to check references.
    • Single-cell array method: Enter =SUMPRODUCT(--(INT(date_range)=date_criterion)), where date_range is the locked full column range and date_criterion is the date (for example 1/20/2014). SUMPRODUCT handles the array without Ctrl+Shift+Enter, and the double negative (--) converts TRUE/FALSE to 1/0. Use parentheses around the comparison so it calculates before negation.
    • To verify the array result, select the INT(...) portion in the formula bar and press F9 to see the evaluated serial numbers, then press Escape or Ctrl+Z to restore the formula.
  • The speaker's example result was 18 matching dates for the array method, while the plain COUNTIFS returned zero, and he had already arrived at 18 using the helper-column COUNTIFS method earlier.
  • The speaker notes that the helper column is the easiest approach with COUNTIFS, while the array approach avoids extra columns but is less intuitive.

Bottom Line

When dates carry time stamps, count by integer date: either add an INT helper column for COUNTIFS or use the SUMPRODUCT-INT array formula to get the same result in a single cell.