Formula Help

[Irwell]

Hi,

I've done this formula but it's returning an incorrect answer, any ideas what's wrong with it.

Column E in contacts is the contact date and time in this format 13/11/2017 12:10

Column F is the contact type and is text

Cell A9 is contact type and is text.

=COUNTIFS(Contacts!$E:$E,">="&DATE(2018,10,1),Contacts!$E:$E,"<="&EOMONTH(DATE(2018,10,1),0),Contacts!$F:$F,$A$9)

Is it something to do with the fact that my dates have the times in?

 

[Irwell]

Right, I've just established that it's not recording any on the 31st October so it must be something to do with my formula? Any help greatly appreciated, thank you.

 

[RoryA]

The time parts are the issue, since your end criterion basically 31st October at 12am. Change it to:

"<"&EOMONTH(DATE(2018,10,1),0)+1

so it will return anything before 1st November.

 

[Irwell]

The time parts are the issue, since your end criterion basically 31st October at 12am. Change it to:

"<"&EOMONTH(DATE(2018,10,1),0)+1

so it will return anything before 1st November.

Worked a treat thank you.

 

[Irwell]

Found a problem with the formula - Our contacts are being put in at midnight so 1st November 00:00 and it's bringing those through, going forward I could ask that correct times are used but it doesn't help historically.

 

[RoryA]

It won't bring those through as it's looking for <1st Nov, not <=1st Nov.

 

[Irwell]

It won't bring those through as it's looking for <1st Nov, not <=1st Nov.

It's just one column when I've re-ran it today, it returns 73 but when I run the pivot to have a look at it I can only see 72 which is really odd.

 

[RoryA]

Without your data I couldn't really say why that might be.