Change project complete date based on late tasks

Theresa Bruns

I am creating a project sheet that has a projected completion date and the actual completion date. I want the projected completion date to remain the same and the actual completion date to change if any of the tasks are late. Each group of tasks has a due date based on the projected completion date, i.e. Setup group must be completed 10 days before projected completion date and Account Creation group must be completed 5 days before projected completion date.

Is there a way to change the actual completion date based on number of days a task(s) is late? For example if the first group of tasks is due on March 7th, but they aren't complete until March 10th, is there a formula that will change the actual completion date to reflect the 3 day delay?

I created a mock up on Excel and it is attached. Thank you as always for your help!

Actual Completion Date.xlsx

GrahamWelsby

Hi Theresa,

Would it be better to use either a formula to see if the individual tasks are completed, then add the date it for the actual completed date. Or use the 'Record a Date' automation utilising conditions of the other tasks? This way the 'Actual Completion Date' will get automatically filled in for you.

you could run another column calculating if TODAY() date is greater than the 'Estimated' date and flag up as a 'Late' Status?

Thanks

graham

Theresa Bruns

Thank you Graham. It sounds like the second option you provided would be the best. I'm a little unclear on the instructions - "you could run another column calculating if TODAY() date is greater than the 'Estimated' date and flag up as a 'Late' Status?" Can you provide more details on how to set that up? The new column I set up would need to reflect the incomplete status and the amount of days later than the due date.

Thanks!

GrahamWelsby

Hi Theresa,

You can maybe run a couple of columns if this is the case. Status & NoDaysLate

In Status something like "=IF(EstimatedDate@row >= TODAY(), "", "LATE")" This will stay blank if the date is less than today, but late if the date has passed.

In NoDaysLate would be, "=IF(Status@row = "LATE", TODAY() - EstimatedDate@row, "") This will remain blank if the project is not flagging Late, but give you no days if it's showing blank from the estimated finish day to today... if you only want to include the working days, wrap TODAY() - EstimatedDate@row in a NETWORKDAYS statement.