I have a 4 step process for different requests (a and b in the table). The process should be completed in the order from 1 to 4. That means that to complete 3 I need to complete 2 before. The last step with no completion date should be the active step. The problem is that I am missing dates in between. For example, for request (a) I don't have the date when step 2 was completed and therefore it appears to be active, but I can see when step 3 was completed. In that case I want to get a state where if the last step (step 4) is active, all other steps appear as completed. But if that step is not active everything should keep the status (completed - if there is a date, Active - if the previous date is available, and waiting if the previous date is not available.
I have tried the following formula:
IF(AND(Table[Processstep]="4",table[Fortschritt]="active"), "Active", IF(Table[Processstep]<>"4","completed",table[status])
However that formula, wil work for request a, but not for request b. I will be very thankful if you can give me an idea on how to solve that.
|Request||Process step||Date Completed||Status||Wanted Status|
|b||3|| ||Waiting ||Waiting |
|b||4|| ||Waiting ||Waiting |