Calculating lateness

David Dunn

I'd like to indicate when activities start slipping behind baseline start dates, using conditional formatting. To do this, however, I need to be able to calculate the difference between the Actual start date (entered manually) and the Baseline start date (locked). What is a formula to do this calculation?

 

 

Gecko

Hi David

I am certainly not an expert but i just tried this.

Create 3 columns. Baseline date, Actual Start, Diffference

In the difference column sum between Baseline and Actual. It is either a positive or negative answer.

In conditional format...If Difference is less than 0 then apply this format to the Task column etc!

I would be keen to know if it solves your issue.

Brett Evans

I like Gecko's answer.

JamesR

You could also make the third column a Flag and use an If function:

=IF([Actual Date]# > {Baseline Date]#,1,0)

David Dunn

Yay Gecko! Now it seems so obvious, I don't know why I was looking at more complex date function formulae. With the simple difference I can achieve my goal: conditional format the difference in shades of green or red, depending on the deviation from the baseline start date.

 

JamesR: Thank you also for your suggestion. It also works as a single flagging signal (on/off, rather than the gradation of the above suggestion).

 

May 2017 continue to stimulate your creative problem solving!

Mike Andreas

I posted this answer on another thread, but I think it may help to repost it here.

 

The following input columns are required (named exactly as I show):

 

Proj Mgr Curr Conf Level of On Time Completion

Actual Percent Complete

Start Date

End Date

Date Completed

 

The following columns with the following equations are required (exactly as named here):

Days Until Start; =[Start Date]2 - TODAY()

 

Days Until Due; =[End Date]2 - TODAY()

 

Planned Percent Complete Per Timeline; =IF(TODAY() - [Start Date]2 < 0, 0, IF([Actual Percent Complete]2 = 1, 1, IF(((TODAY() - [Start Date]2) / ([End Date]2 - [Start Date]2)) >= 1, 1, ((TODAY() - [Start Date]2) / ([End Date]2 - [Start Date]2)))))

 

Detailed Status; =IF(AND([Planned Percent Complete Per Timeline]2 - [Actual Percent Complete]2 > 0.1, [Planned Percent Complete Per Timeline]2 <> 1, [Proj Mgr Curr Conf Level of On Time Completion]2 = "Medium"), "In Progress - At Risk (Medium Confidence)", IF(AND([Planned Percent Complete Per Timeline]2 - [Actual Percent Complete]2 > 0.1, [Planned Percent Complete Per Timeline]2 <> 1, [Proj Mgr Curr Conf Level of On Time Completion]2 = "High"), "In Progress - On Track (High Confidence)", IF(AND([Actual Percent Complete]2 <> 1, [Date Completed]2 <> ""), "TASK NOT COMPLETED - DELETE EXISTING DATE", IF(AND([Actual Percent Complete]2 = 1, [Date Completed]2 = ""), "ENTER COMPLETION DATE", IF(OR(AND([End Date]2 - TODAY() < 0, [Actual Percent Complete]2 <> 1), ABS([Actual Percent Complete]2 - [Planned Percent Complete Per Timeline]2) >= 0.25), "In Progress - Off Track", IF([Date Completed]2 - [End Date]2 > 0, "Completed - Late", IF(AND([Planned Percent Complete Per Timeline]2 = 1, [Actual Percent Complete]2 = 1), "Completed - On Time", IF(AND([Days Until Start]2 > 0, ABS([Actual Percent Complete]2 - [Planned Percent Complete Per Timeline]2) <= 0.1), "Not Started", IF(AND(ABS([Actual Percent Complete]2 - [Planned Percent Complete Per Timeline]2) > 0.1, ABS([Actual Percent Complete]2 - [Planned Percent Complete Per Timeline]2) <= 0.25), "In Progress - At Risk", "In Progress - On Track")))))))))

 

Status; =IF(OR([Detailed Status]2 = "In Progress - At Risk", [Detailed Status]2 = "In Progress - At Risk (Medium Confidence)"), "Yellow", IF(OR([Detailed Status]2 = "In Progress - Off Track", [Detailed Status]2 = "TASK NOT COMPLETED - DELETE EXISTING DATE"), "Red", IF(OR([Detailed Status]2 = "Completed - On Time", [Detailed Status]2 = "Completed - Late"), "Blue", "Green")))

 

Note that the two "Percent Complete" columns need to be formatted as percent.  The status column needs to be formatted as the symbol, specifically the red, yellow, green, and blue traffic lights.  Note that these equations show red if the variance between planned and actual percent complete is more than 25%, yellow if between 10 and 25%, green if less than 10% or task hasn't been due to start yet, and blue if it is complete, either on time or late.  See screenshot included below as an example.

 

Take care to keep an eye on the rows with Detailed Status of "...High/Medium Confidence)", as Planned Percent Complete can creep to 100%, but the Status ball will show Yellow or Green up to PPC = 99%.  To be clear, if PPC = 100% but the actual is anything less, the Red status ball DOES appear.

 

 

Note that for the above equations to work, that they must be pasted into row 2.  Also, be sure not to include the semi-colons I included after the equation-driven column names - those could trip you up if not careful.

 

Also, if you want to use filters to pull for example, red and yellow rows that are tasks only (not parent rows that by themselves don't reflect real work), you will need the additional column with the additional equation, and select "0", since that identifies the row as being the "lowest level child".

 

Is Child?; =IF(COUNT(CHILDREN()) > 0, 1, 0)

 

Note about "Confidence..." column:

A situation may exist where a task should be 90% complete according to the timing defined (ex: given duration is 10 days, and 9 days have passed), has an effort of 2 hours, but is currently 0% complete.  In this scenario, my above equations would return the red status ball, but it may be very possible that the work was always planned to complete on the last day of the 10 days.

 

This is a drop down field, with the values of N/A, Low, Medium, and High.  The way I am using this is to assign N/A to parent rows and also tasks with Detailed Status of "Not Started".  For tasks that have a Planned vs Actual % Complete variance of greater than 10%, Planned % complete not equal to 100%, and an on time confidence level of Medium, Detailed Status results in "...(Medium Confidence)" per below.

 

 

Similar parameters, but confidence is "High", results in the following:

 

Note the conditional formatting I applied to the confidence level column, with the rules as shown:

 

This system has been invaluable for me and my organization in keeping on top of tasks in very large sheets as time creeps along and tasks "silently" become due.  Let me know if the equations work for you.