COUNTIFS with greater than or equal to and less than or equal to date range

Aloha AAHA

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".

Genevieve P.

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

Genevieve P.

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

Intern98

=COUNTIF([Created Date]:[Created Date],AND(DATE(2022, 1, 1)<=,DATE(2022, 1, 31)>=))

Aloha AAHA

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.

Genevieve P.

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

Aloha AAHA

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!

Genevieve P.

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