Need help with a Formula

Platform discussions Special workflows & use cases
1

I need help with creating a formula please. I just can’t seem to get it right!
I have a formula column named “Date of Next Visit”, which is conditional upon the customer’s “Service Level” status column (options 1, 2, 3 or 4) the next visit will be in xx days from the Date of the Last Visit. Each status has a different number of days. 1 = 14 days, 2 = 30 days… and so on. I have been trying to use an IF function statement:
IF({Service Level}=“1 - Platinum”))FORMAT_DATE(ADD_DAYS({Date of Last Visit}, 14))

Can anyone please suggest what formula I should be using? This is an area I am not very experienced in. Thanks.

2

Hi Julie,

I f you had only 3 levels, it would look like this:

IF({Service Level}="1 - Platinum",
   FORMAT_DATE(ADD_DAYS({Date of Last Visit}, 14),"YYYY-MM-DD"),
        IF({Service Level}="2 - Gold",
             FORMAT_DATE(ADD_DAYS({Date of Last Visit}, 30),"YYYY-MM-DD"),
             FORMAT_DATE(ADD_DAYS({Date of Last Visit}, 45),"YYYY-MM-DD")
       )
)

 

And with 4 levels:

IF({Service Level}="1 - Platinum",
   FORMAT_DATE(ADD_DAYS({Date of Last Visit}, 14),"YYYY-MM-DD"),
        IF({Service Level}="2 - Gold",
             FORMAT_DATE(ADD_DAYS({Date of Last Visit}, 30),"YYYY-MM-DD"),
              IF({Service Level}="3 - Silver",
                  FORMAT_DATE(ADD_DAYS({Date of Last Visit}, 45),"YYYY-MM-DD"),
                  FORMAT_DATE(ADD_DAYS({Date of Last Visit}, 60),"YYYY-MM-DD")
             )
       )
)

Want to take your formulas to the next level? Try the Advanced Formula Booster, the app that reinvents formulas in monday.

  • Create formulas without using the Formula column (and avoid its limitations)
  • Build formulas involving data from the previous item, the next item, the sub-items, the parent item, even items in the same group or the same board.
  • In one formula, update multiple columns from multiple items.

Check our blog for real use cases.

1 Like