CountIF Symbol & Between 2 Dates

raymond.j.riosiii94806

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")?

Paul Newcome

Try somethign like this...

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

Gil Nash

Gil Nash

I forgot to rename the "Flags" column

MCorbin

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.

raymond.j.riosiii94806

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

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

Try somethign like this...

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