COUNTIFS with greater than or equal to and less than or equal to date range
Hi,
I'm trying to run a COUNTIFS on a Smartsheet that has a "Created Date" when a user submits a form. I want to count the number of submissions within a given timeframe, i.e., the month of January.
I've tried the following formula:
=COUNTIFS({In-Flight IFM Page Request - Created Date}, >=DATE(2022, 1, 1), {In-Flight IFM Page Request - Created Date}, <=DATE(2022, 1, 31))
Which gives me 103. But when I run a filter with the same date range on the actual Smartsheet, my count is 107, which I've confirmed is correct for that range. Is there something I'm missing?
When I run it with greater than 2021/12/31 and less than 2022/2/1 I also get the 103.
Is there a way to figure out where the missing 4 entries are?
FYI, I ran the same run on February 2022 and my COUNTIFS pulled "80" when the actual is "78".
Best Answers
-
Hi @Aloha AAHA
Your original formula should be correct:
=COUNTIFS({In-Flight IFM Page Request - Created Date}, >=DATE(2022, 1, 1), {In-Flight IFM Page Request - Created Date}, <=DATE(2022, 1, 31))
However it looks like you're searching a Created Date system column. I expect that the slightly different numbers are due to potential Timezone differences.
Date System Columns will have a display value based on your Personal Settings timezone, however the data is recorded in UTC timezone, which means that the formula is reading past the display text to the actual value beneath.
To get around the timezone issue, I would suggest using a Record a Date workflow to track the date when a row is created.
I hope that helps!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi @Aloha AAHA
This other community post has a formula you could use to translate the Created System date into a regular Date.
Let me know if that works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
=COUNTIF([Created Date]:[Created Date],AND(DATE(2022, 1, 1)<=,DATE(2022, 1, 31)>=))
-
Hi Intern98,
Thank you for answering. I tried the formula as below:
=COUNTIF({In-Flight IFM Page Request - Created Date}, AND(DATE(2022, 1, 1)>=, DATE(2022, 1, 31)<=))
and now I'm getting an #UNPARSEABLE value.
-
Hi @Aloha AAHA
Your original formula should be correct:
=COUNTIFS({In-Flight IFM Page Request - Created Date}, >=DATE(2022, 1, 1), {In-Flight IFM Page Request - Created Date}, <=DATE(2022, 1, 31))
However it looks like you're searching a Created Date system column. I expect that the slightly different numbers are due to potential Timezone differences.
Date System Columns will have a display value based on your Personal Settings timezone, however the data is recorded in UTC timezone, which means that the formula is reading past the display text to the actual value beneath.
To get around the timezone issue, I would suggest using a Record a Date workflow to track the date when a row is created.
I hope that helps!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi @Genevieve P. ,
Thank you for the answer. I think the time zone explanation makes sense, as the display value does not match the calculated value, but the discrepancies depending on which month I calculate differ wildly. Especially since we're -10 hours, I can see how there's great variability there.
I will implement the "Record a Date" workflow going forward, but is there any way to still capture the data I want with the existing rows by converting the formula to UTC somehow? Or converting my display value to UTC (based on options/preferences) in order to get the calculations I need? Or creating a new row based on the face value of the "Created Date" row and/or adding/subtracting 10 hours from the time?
Thank you!
-
Hi @Aloha AAHA
This other community post has a formula you could use to translate the Created System date into a regular Date.
Let me know if that works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!