I have this calculated column formula for stoplights on my SharePoint list and I need just a little help updating/tweaking this particular formula. IF/THEN statements are not my forte.

=IF(OR([Date Needed]<Today,Date+90<Today),"red",IF(OR(Modified+15<Today,Status="project"),"yellow","green"))

The addition or change to this formula is the addition of Status="closed" turns the stoplight to green.

Thank you,


  • What is the exact requirement of your calculated field.? – Ganesh Sanap Dec 11 '18 at 10:08

Try the following:

=IF(Status="closed","green",IF(OR([Date Needed]<Today,Date+90<Today),"red",IF(OR(Modified+15<Today,Status="project"),"yellow","green")))


=IF(OR(Status="closed",Status="cancelled"),"green",IF(OR([Date Needed]<Today,Date+90<Today),"red",IF(OR(Modified+15<Today,Status="project"),"yellow","green")))
  • Worked perfectly...although now a new last minute requirement is to make cancelled green as well. Would this be the correct addition to the formula =IF(Status="closed,cancelled","green",IF(OR([Date Needed]<Today,Date+90<Today),"red",IF(OR(Modified+15<Today,Status="project"),"yellow","green"))) – ChrisL Dec 12 '18 at 6:54
  • @ChrisL no this won't work.. – Arsalan Adam Khatri Dec 12 '18 at 7:18
  • @ChrisL I've updated the answer to include cancelled status as well – Arsalan Adam Khatri Dec 12 '18 at 7:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.