Formula: Date

jgneely72151

Hello,

Working on setting up reminders for Covid Booster alerts.

I have a column I'm tracking called Covid Booster Reminder Date. I've entered a crude formula to calculate dates 6 months out when I realized I needed an IF Statement to include the Johnson dose date to be calculated as well. Those IF formulas can be tricky sometimes. That is, for me anyhow. This is the formula I used: =[Pfizer/Moderna Covid Dose 2]@row + 182.5. I also noticed that my results are off by a day or two, so I know I did something incorrect.

Can someone assist me with constructing a formula that will let me know the date 6 months out to record expiry if there is a date in either the Pfizer/Moderna Covid Dose 2 OR Johnson-Single dose columns? The properties for the reminder date column is Date.

And then, how do I have the cell highlight those dates 2 weeks before it expires.

Below is a snippet of my sheet:

Any help is greatly appreciated!

JBrinnen

Hi - here is a formula that will calculate a reminder date based on a starter date in either the Pfizer/M_#2 OR the J&J 1-dose columns:

=IF(NOT(ISBLANK([P/M Dose_2]@row)), [P/M Dose_2]@row + 182.5, IF(NOT(ISBLANK([J&J Dose_1]@row)), [J&J Dose_1]@row + 182.5, "NO Booster Due"))

I kept your half-year approximation of 182.5 as I'm not sure going deeper into the "off by a day or 2" result adds significant value.

Once the formula is in and you have results, you can use Conditional formatting on the Booster Due column using "In the Next [x days]" condition... I used in the next 14 days.

RESULTS:

Hope that helps!

JB