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.? Dec 11, 2018 at 10:08

1 Answer 1


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")))
    – user79745
    Dec 12, 2018 at 6:54
  • @ChrisL no this won't work.. Dec 12, 2018 at 7:18
  • @ChrisL I've updated the answer to include cancelled status as well Dec 12, 2018 at 7:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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