Formula for project status based on where a project is at

belmer

How would I write a formula to automatically populate a status column with red, yellow, or green based on where the project is at? In the screen shot below the Implement Asset Works is on-track because the gantt bar is filled in to the dotted line. The Implement Salesforce is behind schedule since the gantt bar is only partially filled in.

What I would like to do is have the Status be green if the project is on-track or less than 1 week behind. If the project is between 1-3 weeks behind then yellow. If the project is more than 3 weeks behind then red. Thoughts on how to accomplish this?

David Joyeuse

Hi @belmer

You need to calculate different things first here to do what you want.

NETWORKDAYS([Start date]@row, TODAY()) / NETWORKDAYS([Start date]@row, [End date]@row) will get you the theorical % of the project.

(5 / (NETWORKDAYS([Start date]@row; [End date]@row))) will get you the % to do within a week (considering 5 working days within a week).

(15 / NETWORKDAYS([Start date]@row; [End date]@row)) will get you the % to do within 3 weeks.

That makes a lots of calculation to do within one cell. So I would suggest using some helper cells to make it easier here with shorter and quicker formulas. But anyway, here it is.

=IF(NETWORKDAYS([Start date]@row, TODAY()) / NETWORKDAYS([Start date]@row, [End date]@row) - (5 / (NETWORKDAYS([Start date]@row; [End date]@row))) <= [% Complete]@row, "Green", IF(NETWORKDAYS([Start date]@row, TODAY()) / NETWORKDAYS([Start date]@row, [End date]@row) - (15 / NETWORKDAYS([Start date]@row; [End date]@row)) <= [% Complete]@row, "Yellow", "Red"))

Hope it helped!

David Joyeuse

Hi @belmer

You need to calculate different things first here to do what you want.

NETWORKDAYS([Start date]@row, TODAY()) / NETWORKDAYS([Start date]@row, [End date]@row) will get you the theorical % of the project.

(5 / (NETWORKDAYS([Start date]@row; [End date]@row))) will get you the % to do within a week (considering 5 working days within a week).

(15 / NETWORKDAYS([Start date]@row; [End date]@row)) will get you the % to do within 3 weeks.

That makes a lots of calculation to do within one cell. So I would suggest using some helper cells to make it easier here with shorter and quicker formulas. But anyway, here it is.

=IF(NETWORKDAYS([Start date]@row, TODAY()) / NETWORKDAYS([Start date]@row, [End date]@row) - (5 / (NETWORKDAYS([Start date]@row; [End date]@row))) <= [% Complete]@row, "Green", IF(NETWORKDAYS([Start date]@row, TODAY()) / NETWORKDAYS([Start date]@row, [End date]@row) - (15 / NETWORKDAYS([Start date]@row; [End date]@row)) <= [% Complete]@row, "Yellow", "Red"))

Hope it helped!