Getting a COUNTIFS Formula to recognise a Date Field
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
Best Answer
-
Hi Matt,
Try replacing the "14/06/21" with the following:
DATE(2021, 06, 14)
Let me know if it works!
Best,
Heather
Answers
-
Hi Matt,
Try replacing the "14/06/21" with the following:
DATE(2021, 06, 14)
Let me know if it works!
Best,
Heather
-
Thank you Heather, that's worked perfectly!!!! Thank you so much.
-
Glad it worked!
Best,
Heather
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!