Countif with EOMONTH

0
votes

EOMONTH doesn't include end of month days with time stamp format

I am doing a countifs, with EOMONTH and the last day of the month dates with time stamp format is not being counted. How can I get around this issue? For example 4/30/2019 2:56:00 PM is not count

COUNTIFS(Sheet2!$A$1:$A$4,">="&Sheet1!B2,Sheet2!$A$1:$A$4,"<="&EOMONTH(Sheet1!B2,0))

B2=4/1/19
A12:A4
4/30/19 2:43 AM
4/30/19 2:43 AM
4/30/19 2:43 AM

I would expect this formula to return a count of 3

1
excel

1 Answers

1
votes

EOMONTH returns a date without time. 4/30/2019 00:00:00. so being less than or equal will not return true for 4/30/19 2:43 AM as it is greater.

Use 

"<"&EOMONTH(Sheet1!B2,0)+1

Which now asks if it less then 05/01/2019 00:00:00.

Like this:

=COUNTIFS(Sheet2!$A$1:$A$4,">="&Sheet1!B2,Sheet2!$A$1:$A$4,"<"&EOMONTH(Sheet1!B2,0)+1)

]