I have the following data and i am trying to create conditional formulas showing if an item is currently either behind or on schedule by highlighting the Status column green or red. I tried a formula for green that worked if an item was always on schedule at each Date point. It doesn't work if an item has ever been behind schedule but is now back on schedule i.e Items D and E.
Currently my conditional format formulas are:
Currently my conditional format formulas are:
I'm stuck here and can't figure out a formula that will give the current colour as shown in the Status column. Does anyone have a suggestion for improving those formulas?
Green =IF((G2>F2)*1+(E2>D2)*1+(C2>B2)*1=0,1,0)
Red =IF(G2>F2,1,IF(E2>D2,1,IF(C2>B2,1,0)))
<tbody>
</tbody>
Date 1 Scheduled Date 1 Actual Date 2 Scheduled Date 2 Actual Date 3 Scheduled Date 3 Actual Status Colour I currently have Item A 5-Apr-13 5-Apr-13 5-May-13 5-May-13 5-Jun-13 5-Jun-13 Green Green Item B 10-Apr-13 10-Apr-13 10-May-13 11-May-13 10-Jun-13 Red Red Item C 15-Apr-13 15-Apr-13 15-May-13 16-May-13 15-Jun-13 16-Jun-13 Red Red Item D 20-Apr-13 21-Apr-13 20-May-13 21-May-13 20-Jun-13 20-Jun-13 Green Red Item E 25-Apr-13 26-Apr-13 25-May-13 25-May-13 25-Jun-13 Green Red
<tbody>
</tbody>