How to build an IF formula based on a date field

edited 07/27/22 in Formulas and Functions


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!



Best Answer


  • Brent Wilson
    Brent Wilson ✭✭✭✭✭


    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.

  • 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
    Paul Newcome ✭✭✭✭✭✭
    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)))))

  • Thank you so much Paul!!!

    This worked!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!