Excel dates and a means to record date milestones

[darkgen]

OK, so I have a flow which has various key date milestones which will be calculated from an inception date and I need to capture whether each milestone SLA is hit as it goes through that flow.

For example;

Date	MileStoneProgress1	MilestoneSLA1	MileStoneProgress2	MilestoneSLA2
25/01/2014

The MileStoneProgress cells contain either 0,1,2 which refers to Submission / InProgress / Completed. When that cell gets changed to a 2 (Completed) it executes a formula similar to today()-B2<10 to test whether it was completed within that particulars milestoneSLA (10days in this example) from the inception date.

Is this easily achievable or am I likely going to need to write some code or perhaps a macro to achieve this?

EDIT, actually I do need to expand on this. I would like to show if MileStoneSLA is past the SLA regardless of whether MileStoneProgress is set to 2 (completed) or not. I just need a means capture whatever the result is once MileStoneProgress is changed to 2 (completed).


Thanks in advance, Paul.

 

[Poolybit]

Just need a few bits clarifying, what do you want to happen to the resulting calculation of the difference between the starting date and the SLA date? Do you just want it highlighted red or something? Would this date be based upon the date that it was changed or would you enter the date that you completed the milestone in question?

 

[darkgen]

So to answer you directly. Yes a visual indicator via conditional formatting based on the cell value is easy enough.

So for instance, a new row of data is submitted, at that point all MileStoneSLA would show TRUE (using the formula above) and conditional formatting can be used to show that as a visual green traffic light. The challenge is locking the result of that cell the moment you then select MileStoneProgress as complete (2).

EDIT - S;
MileStoneSLA1 would be today()-B2<10
MileStoneSLA2 would be today()-B2<20
MileStoneSLA3 would be today()-B2<30

In total there is a 30 day SLA, each milestone broke into 10 days increments. MileStoneSLA1 is completed on the 6th day. MileStoneProgress1 is changed to completed (2) | the result in cell MileStoneSLA1 at that point is then locked.

Clear as mud? I am probably doing this very elbow about backwards.

Cheers, Paul.