Calculate Date Column dependent on number column

Tshelton · 01/26/22

Hi Everyone,

I am looking for assistance writing a formula for one of my sheets. I have the following columns:

Visit Date

Screening Score

6 Month F/u Date

12 Month F/u Date

What I am looking to do is have the 6 follow up dates calculate from the visit date only if the screening score is above 9. If it is 9 or below, I would like the 6 month field to remain blank and the 12 month field to populate.

I will admit that formulas are not my strong suit. I have tried this a few different ways and keep getting error messages. Any assistance is greatly appreciated! Thank you!

Jeff Reisman · 01/26/22

The simplest way to do this is to add 180 or 365 days to the visit date based on the screening score.

I tested this formula and confirmed it works. Put this one in the 6 month follow up column.

=IF([Screening Score]@row > 9, [Visit Date]@row + 180, "")

In English, if the screening score is greater than 9, add 180 days to the visit date, otherwise leave the cell blank.

For the 12 month, same thing, just change the parameters to screening score less than or equal 9 and add 365 days:

=IF([Screening Score]@row <= 9, [Visit Date]@row + 365, "")

Tshelton · 01/26/22

Thank you! Worked like a charm. I was forgetting the "" at the end. I appreciate the help!

Jeff Reisman · 01/26/22

https://community.smartsheet.com/discussion/comment/317498#Comment_317498

Glad I could help. If you could mark my answer as Accepted, that would be great!

Calculate Date Column dependent on number column

Answers

Help Article Resources

Categories