# Counting Items With Rolling Dates - Last Day of Month Issue

#### RudeClown

##### Board Regular
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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

##### Well-known Member
Hi RudeClown,

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

#### RudeClown

##### Board Regular
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
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)``

#### RudeClown

##### Board Regular

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
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
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
Glad we could help & thanks for the feedback.

Replies
6
Views
67
Replies
1
Views
201
Replies
6
Views
240
Replies
3
Views
123
Replies
4
Views
215

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.

1,152,043
Messages
5,767,797
Members
425,436
Latest member
MSPaperclipMan

### 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.

### Which adblocker are you using?

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

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