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")?
Try somethign like this...
=COUNTIFS([Flag Column]:[Flag Column], 1, Created:Created, AND(DATEONLY(@cell) >= [Encounter Date]$2, DATEONLY(@cell) <= [Date Resulted]$2))
I forgot to rename the "Flags" column
Here you go:
=COUNTIFS([Work Related Exposure]@row, 1, [Encounter Date]@row, <=DATE(VALUE("20" + MID([email protected], 7, 2)), VALUE(MID([email protected], 1, 2)), VALUE(MID([email protected], 4, 2))), [Date Resulted]@row, >=DATE(VALUE("20" + MID([email protected], 7, 2)), VALUE(MID([email protected], 1, 2)), VALUE(MID([email protected], 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...
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.