Calculate Date Column dependent on number column
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!
Answers
-
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, "")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thank you! Worked like a charm. I was forgetting the "" at the end. I appreciate the help!
-
Glad I could help. If you could mark my answer as Accepted, that would be great!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!