Counting Items With Rolling Dates - Last Day of Month Issue

[RudeClown]

Good morning! I've got a spreadsheet with two tabs, where the "validation" tab displays summary information pulled from the "data" tab. I've got rolling data displaying counts from the current & previous 12 months which works, except for it does not seem to be counting entries from the last day of the month. The below is the formula that should return a count of closed items from the data tab which were closed in August 2021. The formula as below is returning "7," but the correct number is "8." I have verified that it is one entry on 8/31/21 in column O that is not being counted. I did make sure the column D condition is met. It may be something silly that I am missing. Any thoughts?

=COUNTIFS(Data!D:D,"=Closed",Data!$O:$O,">="&EOMONTH(TODAY(),-2)+1,Data!$O:$O,"<="&EOMONTH(TODAY(),-1))

 

[Toadstool]

Hi RudeClown,

I'd guess one of the "Closed" cells has a spurious character in it, maybe "Closed "?

 

[RudeClown]

Hi RudeClown,

I'd guess one of the "Closed" cells has a spurious character in it, maybe "Closed "?

Thank you for the thought....I had checked that and made sure there was nothing else in the cell. I even deleted "Closed" and re-typed it to be sure.

 

[Fluff]

Do the dates in col O contain a time value? If so try

=COUNTIFS(Data!D:D,"=Closed",Data!$O:$O,">="&EOMONTH(TODAY(),-2)+1,Data!$O:$O,"<"&EOMONTH(TODAY(),-1)+1)

 

[RudeClown]

Do the dates in col O contain a time value? If so try

=COUNTIFS(Data!D:D,"=Closed",Data!$O:$O,">="&EOMONTH(TODAY(),-2)+1,Data!$O:$O,"<"&EOMONTH(TODAY(),-1)+1)

Yes, they do...in past projects that hasn't been the case, so I didn't know it'd be any different this time, but it definitely is. I updated the formulas in all my cells gathering monthly data, and the ones I had identified with last-day-of-the-month data are all accurate now. Thank you for your help! Can you give me a quick run-through of the logic why it didn't work before, and why it does doing it that way?

 

[Fluff]

All dates are stored as numbers, 30 Sep 2021 is 44469 & time is just a decimal number so 6 AM is 44469.25 as it's 1 quarter of a day.
So as soon as you have a time element on the last day of the month 44469.25 is greater than 44469. By using the 1st of the next month & asking for less than it gets rid of the problem.

 

[RudeClown]

Ah okay. I know dates are stored as numbers, but I didn't know that when times are included, it adds a decimal to account for the time. That makes sense. Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.