Rolling 90 Day Formula
Hello,
I need to write a formula that will check a box if a date is in the next 90 days, and uncheck if the date has passed.
What I have so far is:
=IF(Start@row <= TODAY(90), 1,0)
So, if the Start date is less than or equal to 'Today' or the next 90 days, then check the box. If the Start date is not in the next 90, then leave the box unchecked.
My formula is not un-checking the box for dates in the past (past 'Today'), and is checking the box when the date is blank, which is not a desired outcome.
I tried a nested if statement to uncheck the box if the date is in the past, and blanks, but have not had success yet.
Best Answer
-
Try
=IF(ISDATE(Start@row), IF(Start@row <= TODAY(90), IF(Start@row < TODAY(), 0, 1), 0))
Answers
-
Try
=IF(ISDATE(Start@row), IF(Start@row <= TODAY(90), IF(Start@row < TODAY(), 0, 1), 0))
-
That works! Thank you @Paul H
For my own understanding:
First IF(ISDate is checking to see if the Start@Row is a date, if yes, evaluate second statement, If not, leave box unchecked
the second statement is 'within 90 days of today' IF(Start@row <= TODAY(90),
The third IF is 'saying' - If the start date is less than today, uncheck the box, if not then check the box.
-
@Matthew_Lanterman Your breakdown is correct.
Check its a date, check its within 90, check its not in the past
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!