# 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

Tags:

• @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)))))

• 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!