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

Tags:

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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.
    Genevieve P. Employee Admin
    Answer ✓

    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

Answers

  • Intern98
    Intern98 ✭✭✭

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

  • Aloha AAHA
    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.
    Genevieve P. Employee Admin
    Answer ✓

    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
    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.
    Genevieve P. Employee Admin
    Answer ✓

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!