Counting Items With Rolling Dates - Last Day of Month Issue

RudeClown

Board Regular
Joined
May 31, 2016
Messages
50
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))
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,448
Office Version
  1. 2016
Platform
  1. Windows
Hi RudeClown,

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

RudeClown

Board Regular
Joined
May 31, 2016
Messages
50
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,065
Office Version
  1. 365
Platform
  1. Windows
Do the dates in col O contain a time value? If so try
Excel Formula:
=COUNTIFS(Data!D:D,"=Closed",Data!$O:$O,">="&EOMONTH(TODAY(),-2)+1,Data!$O:$O,"<"&EOMONTH(TODAY(),-1)+1)
 
Solution

RudeClown

Board Regular
Joined
May 31, 2016
Messages
50

ADVERTISEMENT

Do the dates in col O contain a time value? If so try
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,065
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 31, 2016
Messages
50
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,065
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,042
Messages
5,767,793
Members
425,435
Latest member
cmardaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top