How to build an IF formula based on a date field
Hello,
I am looking for help in building an IF formula that will return a number value, based on how far in the past a date is (using a date field).
Where a date field is populated and depending on the date, a number will be chosen :
1 = a date that falls in the last 2 months from today
2 = a date that falls between 2 and 4 months from today
3 = a date that falls between 4 and 6 months from today
4 = a data that falls between 6 and 8 months from today
5 = a date that is greater then 8 months from today
The only part I've figured out is to use the VALUE function so my other formulas read the field.
Any help would be greatly appreciated!
Natalie
Best Answer
-
@Talie Try this...
=IF(Date@row <> "", IF(Date@row <= TODAY(-240), 5, IF(Date@row <= TODAY(-180), 4, IF(Date@row <= TODAY(-120), 3, IF(Date@row <= TODAY(-60), 2, 1)))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
First I would convert Months to Days .. so an average of 30.4375 days/month
=IF(If [Date]@row > Today()+(8*30.4375), 5, IF(If [Date]@row > Today()+(6*30.4375), 4, IF(If [Date]@row > Today()+(4*30.4375), 3, IF(If [Date]@row > Today()+(2*30.4375), 2, 1))))
If you have errors take the math our and you might need to use whole numbers
This assumes 31 days per month which is probably close enough
=IF(If [Date]@row > Today()+248, 5, IF(If [Date]@row > Today()+186, 4, IF(If [Date]@row > Today()+124, 3, IF(If [Date]@row > Today()+62, 2, 1))))
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
-
Thanks for the reply Brent.
I tried both options but no matter what date I change my "Date Approved by Intake" to, the formula column will only return a value of 1...any suggestions?
-
@Talie Try this...
=IF(Date@row <> "", IF(Date@row <= TODAY(-240), 5, IF(Date@row <= TODAY(-180), 4, IF(Date@row <= TODAY(-120), 3, IF(Date@row <= TODAY(-60), 2, 1)))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you so much Paul!!!
This worked!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 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
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!