Using EOMONTH inside of a DATE function in a COUNTIFS formula

[RudeClown]

I am working on creating a formula for a series of cells that will show rolling data in a specific category for the current month plus the previous 12 months. Because of the rolling nature, I need to be able to use “end-of-month” as part of the formula for the ending date of the month for each cell to search (unless there’s a better way?). Here’s what I came up with so far (in the first cell behind Current Month, which would currently be May), but it’s not returning an accurate count. It’s giving me “52,” when it should be giving me “30“. Any ideas what I’m doing wrong here?

=COUNTIFS('E-Mail Log'!A:A,">="&DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),'E-Mail Log'!A:A,"<="&DATE(YEAR(TODAY()),MONTH(TODAY())-1,EOMONTH(TODAY(),-1)))

Regarding another approach, what if I were to simply use the date “31” as the stopping point for the search instead of trying to figure the EOM? Would this cause any issues in the months that have less than 31 days? I added the formula to test out a couple of these months which returned accurate answers, but are there any unforeseen issues this can cause? The formula works, and looks like this (for one month back):

=COUNTIFS('E-Mail Log'!A:A,">="&DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),'E-Mail Log'!A:A,"<="&DATE(YEAR(TODAY()),MONTH(TODAY())-1,31))

Any help or suggestions would be truly appreciated =)

 

[Eric W]

The EOMONTH function returns a full date (6/30/2016), not just the day (30). So including it within a DATE function won't work as you have it coded. You could try:

=COUNTIFS('E-Mail Log'!A:A,">="&EOMONTH(TODAY(),-2)+1,'E-Mail Log'!A:A,"<="&EOMONTH(TODAY(),-1))

which will count up everything in May. The EOMONTH(TODAY(),-2) calculates the end of month date 2 months back (April 30, 2016), then adds 1 to that, getting May 1, 2016. The second EOMONTH returns May 31, 2016. Change the -1 at the end to 0 and it will sum up everything in May and June.

And yes, you could get problems if you use 31 in DATE as the end of a month. If the month has less than 31 days, then Excel will helpfully calculate the next day, or July 1. DATE(2016,2,31) will return May 2, 2016.

Hope this helps!

 

[RudeClown]

That worked perfectly, as was able to adapt the logic to several different calculations. Thanks so much for your help!

 

[Jonmo1]

And yes, you could get problems if you use 31 in DATE as the end of a month. If the month has less than 31 days, then Excel will helpfully calculate the next day, or July 1. DATE(2016,2,31) will return May 2, 2016.

This is why I like to use the First Day of the Next month (and use < instead of <=) when doing a monthly sum/count/average IF..

Instead of
>=1/1/2016 AND <=1/31/2016
I do
>=1/1/2016 AND <2/1/2016

This logic is also very helpful when the dates in the range you're counting also contain time values.

i.e. 1/31/2016 15:30 Is NOT <= 1/31/2016, but it IS < 2/1/2016