How to build an IF formula based on a date field

Talie

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

Paul Newcome

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

Brent Wilson

@Talie

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

Talie

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?

Paul Newcome

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

Talie

Thank you so much Paul!!!

This worked!

Paul Newcome

Happy to help. 👍️