Problem working with dates

Question

Column H contains data sorted by date (28/12/25 to 11/01/26)

Column F contains the date to select the range, in this case F6 to F4

The answer should be 12, but the formula gives 0.

Answer 1

The issue with your formula is that Excel is treating the dates as text strings rather than actual dates. In Excel, COUNTIFS with comparison operators (>= and <=) only works correctly with real date values, not text that looks like dates. In your formula:

=COUNTIFS($H$3:$H$16,">="+$F$6,$H$3:$H$16,"<="+$F$4)

if any of the cells in column H or F are text, it will return 0.

To fix this, you need to ensure that both the date range and criteria cells are recognized as proper Excel dates. You can do this by:

Checking the format of the cells (Column F and Column H) and changing them to Date.

If they are stored as text, convert them to dates using DATEVALUE like this:

=COUNTIFS($H$3:$H$16, ">=" & DATEVALUE(F6), $H$3:$H$16, "<=" & DATEVALUE(F4))

This forces Excel to treat the criteria as dates.

Once all cells are proper dates, your formula should correctly return 12 for the range 28/12/25 to 11/01/26.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Answer 2

Delete post please.