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)))))
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)))))
-
Thank you so much Paul!!!
This worked!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!