How to make this formula? Count how many rows have dates within a timeframe

walcala

I need to calculate how many rows have an APPOINTMENT within 3 days of their DISCHARGE date.

Is there a formula that can get that data? I also need within 7 days as well. I'm a novice at smartsheet formulas. I appreciate your help!

Gillian C

Hi @walcala

I would suggest adding a helper column to start with. I will call it [Within 3 or 7 days]

The formula for this would be

=IF(AND(Appointment@row - Discharge@row <= 7, Appointment@row - Discharge@row > 3), 7, IF(Appointment@row - Discharge@row <= 3, 3, ""))

Then your two further columns

Within 3 days = COUNTIF([Within 3 or 7 days]:[Within 3 or 7 days] = 3)

and

Within 7 days = COUNTIF([Within 3 or 7 days]:[Within 3 or 7 days] = 7)

Hope this helps :)