CountIF Symbol & Between 2 Dates

Can someone help me with a formula that would count all the "Flags" in the "Work Related Exposure" if the "Created" date is between two dates ("Encounter Date" and "Date Resulted")?


Tags:

Best Answer

Answers

  • Gil Nash
    Gil Nash ✭✭✭
    edited 07/29/20

    I forgot to rename the "Flags" column



  • MCorbin
    MCorbin Overachievers Alumni

    Here you go:

    =COUNTIFS([Work Related Exposure]@row, 1, [Encounter Date]@row, <=DATE(VALUE("20" + MID(Created@row, 7, 2)), VALUE(MID(Created@row, 1, 2)), VALUE(MID(Created@row, 4, 2))), [Date Resulted]@row, >=DATE(VALUE("20" + MID(Created@row, 7, 2)), VALUE(MID(Created@row, 1, 2)), VALUE(MID(Created@row, 4, 2))))


    **The Created and Modified dates, while displayed in local time, are actually stored in UTC. SO... if you use them in a formula, you need to parse them out like I did above to make sure you get the date displayed, as opposed to the date in UTC. Where I am (California), at 3 PM local time, UTC changes to the next day, and formulas using the Created or Modified dates (without the formula I use above) will actually result in tomorrow's date.

  • Thank you Gil. I think I may not have been clear. I'm looking for 1 number to calculate in another field.

    I was thinking something like the following:

    CountIF([Work Related Exposure]:[Work Related Exposure],is flagged,AND([Created]:[Created],<>[Encounter Date] and [Date Resulted])

    But I can't figure it out...

  • MCorbin
    MCorbin Overachievers Alumni
    edited 07/29/20

    Sorry - I needed to add..... The formula would be added to each row in a "helper column" and gives you a 1 if your row meets the criteria, or a 0 if it does not. I would then create a total field that sums the helper column.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try somethign like this...

    =COUNTIFS([Flag Column]:[Flag Column], 1, Created:Created, AND(DATEONLY(@cell) >= [Encounter Date]$2, DATEONLY(@cell) <= [Date Resulted]$2))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!