Need a formula for % completed to change status health, as well as % completed to update status

clairemorse09

Hi there,

I am after two formulas:

FORMULA ONE

I need my % Complete to reflect the Status, example below:

0% equals status - Not Started

1 - 99% equals status - In Progress

100% equals status - Complete

FORMULA TWO

I need my Health status to reflect the following:

Completed equals Green

In Progress equals Yellow

Not Started equals Grey

Items that are In Progress or Not Started and it is past the due date, equals Red. I.e. If its 31/03/2023 I want anything that is in Not Started or In Progress that was due 30/03/2023 (and any other date before) to equal RED.

Please help me.....

Nick Korna

Hi @clairemorse09 ,

These should do what you're after.

Formula 1:

=IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row = 1, "Complete", "In Progress"))

Formula 2:

=IF(Status@row = "Complete", "Green", IF([Due Date]@row < TODAY(), "Red", IF(Status@row = "Not Started", "Gray", "Yellow")))

Sample data & output for today (31/03):

Hope this helps, but if you've any questions etc. then just post! 😊

clairemorse09

Hi Nick,

Thanks for getting back to me, but the second formula isnt working, any other ideas?

Thanks