Have an issue when trying to count a values between the dates. For example: Column X date 1/1/2020 Column Y date 15/05/2020
Using formula: =COUNTIFS(X38:X43,"<= 1/2/2020",Y38:Y43,">= 28/2/2020")
It returns 0, but at least 1 should fit the result.
Excel/Speadsheets: How to calculate a count between the dates
Viewed 35
2 Answers
COUNTIFS does not like the space between the = and the dates. It is trying to parse a string and with the space it is looking for a string that has a space on the front of it:
with space:
vs. without the space:
=COUNTIFS(X38:X43,"<=2/1/2020",Y38:Y43,">=2/28/2020")
Behind the scenes, dates in Excel are just numbers, even if they are represented as dates.
You can more clearly see what is going on if the dates are shown as their integer values. (you may need to use VALUE() to prevent format warnings)
Note that the cell format will not change how Excel treats them in formulas.
Related