Getting a COUNTIFS Formula to recognise a Date Field

Matt Wood

Hi All, I've been trawling through the Forum Archives but unfortunately, I can't quite find what I'm looking for. I have a Sheet where I'm asking it to tally 3 columns so i'm using the COUNTIFS Formula. One of the column's is set as a Date/Time column, however the standard COUNTIFS formula can't recognise the date so it will return a value of 0 which I know is incorrect.

I've highlighted in Grey the three columns I'm trying to Count. For the example formula listed below, i'm trying to count passengers on Flight 864 travelling in Business Class on the 14th June:

=COUNTIFS([Inbound Flight Number]19:[Inbound Flight Number]82, "864", [Inbound Air Class]19:[Inbound Air Class]82, "Business", [Return Travel Date]19:[Return Travel Date]82, "14/06/21")

I know the workaround would be to change the Date Column to a 'Text/Number' Format but i'm trying to avoid that if at all possible as collaborators on the Sheet require the ability to add the Date from the Calendar rather than type.

Many Thanks

Matt

Heather Duff

Hi Matt,

Try replacing the "14/06/21" with the following:

DATE(2021, 06, 14)

Let me know if it works!

Best,

Heather

Heather Duff

Hi Matt,

Try replacing the "14/06/21" with the following:

DATE(2021, 06, 14)

Let me know if it works!

Best,

Heather

Matt Wood

https://community.smartsheet.com/discussion/comment/283710#Comment_283710

Thank you Heather, that's worked perfectly!!!! Thank you so much.

Heather Duff

Glad it worked!

Best,

Heather