进程跟踪器 - 公式，vba或条件格式？

I just started to make an Excel ”tracker”, so I can follow how it goes with a process. I have 6 steps in all that I need to track: Step 1, Step 2, Step 3, Step 4, Step 5 and Step 6. I have also a start and deadline date. Step 1-3 I estimated to be finished 20 days before deadline. Step 4 need to be finished 6 days before deadline, Step 5 5 days before deadline.

我刚刚开始制作一个Excel“跟踪器”，所以我可以按照它的流程进行操作。在我需要跟踪的所有内容中，我有6个步骤：步骤1，步骤2，步骤3，步骤4，步骤5和步骤6.我还有一个开始和截止日期。步骤1-3我估计在截止日期前20天完成。第4步需要在截止日期前6天完成，步骤5在截止日期前5天完成。

I need the tracker to show ”Semi-behind” if these is not achieved. If the process is going after the plan Column N just have to show “On track”. If the deadline date is passed, then Column have to say “Behind”. If the difference between start date and deadline date is less than 20 days, column N have to say “ASAP”.

如果没有实现，我需要跟踪器显示“半后”。如果流程在计划列N之后，则必须显示“正常”。如果截止日期已过，则Column必须说“Behind”。如果开始日期和截止日期之间的差异小于20天，则N列必须说“尽快”。

Do you have a good idea how I can make this? With formulas, vba or just conditional formatting?

你知道我怎么做这个吗？使用公式，vba或只是条件格式？

1 个解决方案

#1

I think you can use this formula. Let's says you put Start Date in column L2, ProcessDate in column M2 Deadline in column N2 and the formula in column O2 would be like this:

我想你可以使用这个公式。假设您将开始日期放在第L2列，ProcessDate放在第N2列的第M2列截止日期，而第O2列的公式将如下所示：

=IF(AND((N2-M2)<20,(N2-M2)>0),"ASAP",IF(M2>L2,"On Track",IF(M2>N2,"Behind","Semi-behind")))

Hope this help.

希望这有帮助。