Solved Help with EOMONTH function

August 20, 2018 at 06:55:25
Specs: Windows 10
I have a COUNTIFS formula that is counting the number of records where the created date is <= a date selected by the user:

=COUNTIFS(Data!$K:$K,$B86,Data!$AB:$AB,"",Data!$P:$P,"<="&EOMONTH(National!$W$1,0))

This part of the formula isn't counting one of my records:

,"<="&EOMONTH(National!$W$1,0))

The date contained within National!$W$1 is 31/07/2018 00:00

The record that isn't being counted has the created date 31/07/2018 22:52

With the formula's construction, shouldn't it count everything less than, or equal to 31/07/2018 23:59:59?


See More: Help with EOMONTH function

Reply ↓  Report •

#1
August 20, 2018 at 13:23:38
Without knowing what is in column K and what is in cell B6 can't really tell you what's going on.

Just remember that all three criteria must be matched before it counts.

Assuming that column AB is completely blank, no data,

If there is only One match in column K,
but multiple matches in column P
you only get a count of 1

and conversely

if you have multiple matches in column K
buy only only one match in column P
you still only get a count of 1

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#2
August 20, 2018 at 22:04:38
✔ Best Answer
Another thought,

With the formula's construction, shouldn't it count everything less than, or equal to 31/07/2018 23:59:59?

If the date is really a Date and NOT Text that LOOKS like a Date.

Simplest way to check would be to select the entire P column
and then format as Number, if any remain in a Date Format that is where the error is.
Also, if any of the data in column K has a trailing space or special character
that could also result in a non match, even tho they look the same.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#3
August 21, 2018 at 00:29:24
Hi Mike

Thanks for the responses. I've checked and the data in the column is all dates. I've also checked the other criteria in the COUNTIFS (actually did this before posting but did it again to be sure) and there are no rogue spaces anywhere. As soon as I change the entry from 31/07/2018 22:52 to 31/07/2018 00:00 the count changes from 1 to 2.

I double checked by opening a new sheet and manually typing 31/07/2018 22:52 into cell A1, then typing 31/07/2018 00:00 into B1, then converting both to numbers. You can see that 31/07/2018 22:52 is actually a larger number than 31/07/2018 00:00 which makes sense.

What surprises me though is that when I typed

=EOMONTH(A1,0)
into cell C1, it returns 31/07/2018 00:00. When I played around with dates/times and converted them to numbers it looks like the decimal increases with each 15 minute increase, i.e. to get 43312.98 you have to enter 31/07/2018 23:30 and to get 43312.99 you have to enter 31/07/2018 23:45.

I'll therefore adjust my original COUNTIFS formula as follows to ensure I capture everything from the same date:

=COUNTIFS(Data!$K:$K,$B86,Data!$AB:$AB,"",Data!$P:$P,"<="&EOMONTH(National!$W$1,0)+0.99)

Basically adding the 0.99 at the end.

message edited by ScottV


Reply ↓  Report •

Related Solutions

#4
August 21, 2018 at 06:58:08
Scott,

"Excel stores dates and times as a number representing the number of days since 1900-Jan-0,
plus a fractional portion of a 24 hour day."

For a complete explanation of how Excel stores Dates & Times see here:

http://www.cpearson.com/excel/datet...

Scroll all the way to the top.

In my test sheets, I was not entering any times with the dates, so, I unfortunately, missed it.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

Ask Question