Row B=Status (In Progress, On Hold, or Completed)
Row C= Task Description
Row D= Start Date
Row E= Estimated Completion Date
Row F= Actual Completion Date
Cell I5=Date (Part of a series of dates within the calendar month)
Current Formula: =if(AND(I$5>=$D7,I$5<=$E7),if(and($B7="completed",I$5>$F7),"gray",if(I$5<=today(),"green","gray")),if(And(OR($B7="In Progress",$B7="On Hold"),today()>=$E7,I$5>$E7,today()>=I$5),"Red",""))
Gray represents= expected days task will be worked on
Green represents= actual days task was worked on
Red= extra (non expected) days task was actually worked on
This formula is saying this: Focus on the cells between the start date and estimated completion date of a task. From here, if the task is marked completed and the task was completed earlier than expected, mark the extra days gray. If the date in cell I5 is less than or equal to today's date then mark it green, if not keep it gray.
This is where THE PROBLEM comes in. If a task started on 11/1/2014 and was estimated to be completed on 11/3/2014 but wasn't actually completed until 11/6/2014 then the colors associated with those dates should be as follows: From 11/1-11-3 the cells should be green and from 11/4-11/6 should be red (indicating that these days were extra days spent on the task). All other cells would be blank.
So the problem is, when I mark the task completed the only cells that maintain their color are those green cells between the start date and estimated completion date. I need the red color to remain, so I can visually show that the task took longer than expected. Any suggestions!!!???
Row C= Task Description
Row D= Start Date
Row E= Estimated Completion Date
Row F= Actual Completion Date
Cell I5=Date (Part of a series of dates within the calendar month)
Current Formula: =if(AND(I$5>=$D7,I$5<=$E7),if(and($B7="completed",I$5>$F7),"gray",if(I$5<=today(),"green","gray")),if(And(OR($B7="In Progress",$B7="On Hold"),today()>=$E7,I$5>$E7,today()>=I$5),"Red",""))
Gray represents= expected days task will be worked on
Green represents= actual days task was worked on
Red= extra (non expected) days task was actually worked on
This formula is saying this: Focus on the cells between the start date and estimated completion date of a task. From here, if the task is marked completed and the task was completed earlier than expected, mark the extra days gray. If the date in cell I5 is less than or equal to today's date then mark it green, if not keep it gray.
This is where THE PROBLEM comes in. If a task started on 11/1/2014 and was estimated to be completed on 11/3/2014 but wasn't actually completed until 11/6/2014 then the colors associated with those dates should be as follows: From 11/1-11-3 the cells should be green and from 11/4-11/6 should be red (indicating that these days were extra days spent on the task). All other cells would be blank.
So the problem is, when I mark the task completed the only cells that maintain their color are those green cells between the start date and estimated completion date. I need the red color to remain, so I can visually show that the task took longer than expected. Any suggestions!!!???
