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.

 

[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.