Calculated Days Delayed. weeks delayed

Khanambano

I have to count Days delayed; week delayed. from the dates below. I am not sure what formulas i should use. I was first going with FinishDate - Duration but that wouldn't work since the duration is calculated between Start and Finish.

Genevieve P.

Hi @Khanambano

It sounds like you want to compare the Actual finish dates to the projected Finish date, to see the variance between the two.

In this case, you'll receive an error since all of your Actual finish dates are blank. I would suggest adding an IF statement in the front of your formula to say that if the Actual Finish is blank but the Progress is Complete, return "0", like so:

=IF(AND([Actual Finish]@row = "", Progress@row = "Complete"), 0

Then the next think you'll want to check is if the Progress is anything other than "Complete", as you'll likely want a blank value:

IF(Progress <> "Complete", ""

Otherwise, if the progress IS complete and the Actual Finish is not blank, but also, if the Actual Finish is later than the estimated Finish, we can subtract the Actual Finish from the estimated Finish, like so:

IF([Actual Finish]@row > Finish@row, [Actual Finish]@row - Finish@row

ALTERNATE: If you wanted to only have Working Days, you could use the NETWORKDAYS Function:

IF([Actual Finish]@row > Finish@row, NETWORKDAYS(Finish@row, [Actual Finish]@row), 0

Full Formula Example:

=IF(AND([Actual Finish]@row = "", Progress@row = "Complete"), 0, IF(Progress@row <> "Complete", "", IF([Actual Finish]@row > Finish@row, NETWORKDAYS(Finish@row, [Actual Finish]@row), 0)))

Let me know if this works and is what you were looking for.

Cheers,

Genevieve

Christian G.

I'm not sure if I understand your question.

Duration is in business days only.

Days between finish and start

=[Finish]@row-[Start]@row

Week delay :

=floor([Days delayed]@row/7,1)

Genevieve P.

Hi @Khanambano

It sounds like you want to compare the Actual finish dates to the projected Finish date, to see the variance between the two.

In this case, you'll receive an error since all of your Actual finish dates are blank. I would suggest adding an IF statement in the front of your formula to say that if the Actual Finish is blank but the Progress is Complete, return "0", like so:

=IF(AND([Actual Finish]@row = "", Progress@row = "Complete"), 0

Then the next think you'll want to check is if the Progress is anything other than "Complete", as you'll likely want a blank value:

IF(Progress <> "Complete", ""

Otherwise, if the progress IS complete and the Actual Finish is not blank, but also, if the Actual Finish is later than the estimated Finish, we can subtract the Actual Finish from the estimated Finish, like so:

IF([Actual Finish]@row > Finish@row, [Actual Finish]@row - Finish@row

ALTERNATE: If you wanted to only have Working Days, you could use the NETWORKDAYS Function:

IF([Actual Finish]@row > Finish@row, NETWORKDAYS(Finish@row, [Actual Finish]@row), 0

Full Formula Example:

=IF(AND([Actual Finish]@row = "", Progress@row = "Complete"), 0, IF(Progress@row <> "Complete", "", IF([Actual Finish]@row > Finish@row, NETWORKDAYS(Finish@row, [Actual Finish]@row), 0)))

Let me know if this works and is what you were looking for.

Cheers,

Genevieve

Khanambano

Hi @Genevieve P.

Thank you for the detailed response. When i use the full formula that you have above (also copied below) i am getting Unpershable in return. what could i be doing wrong?

=IF(AND([Actual Finish]@row = "", Progress = "Complete"), 0, IF(Progress <> "Complete", "", IF([Actual Finish]@row > Finish@row, NETWORKDAYS(Finish@row, [Actual Finish]@row), 0)))

when i use the simpler formula; i get the days delayed

when i use the simpler formula; i get the days delayed; but the distinction with the progress cloumn in complete is important.

Genevieve P.

Hi @Khanambano

My apologies! The "Progress" cell reference is missing the row reference (in this case it's @row)

Try adding in @row after the word "Progress" in your formula and it should calculate.

=IF(AND([Actual Finish]@row = "", Progress@row = "Complete"), 0, IF(Progress@row <> "Complete", "", IF([Actual Finish]@row > Finish@row, NETWORKDAYS(Finish@row, [Actual Finish]@row), 0)))

Khanambano

Hi @Genevieve P.

I think im almost there but for some reason i still get unperishable - see example below.

Genevieve P.

Hi @Khanambano

It looks like you have one too many closing parentheses at the end:

, 0))))

You only need 3, like so:

, 0)))

Let me know if that worked, now!

Khanambano

Thank you so much @Genevieve P.

The Days Delayed worked perfectly.

I was hoping and would be grateful if you can help me with two additional formulas for the same sheet.

I am trying to calculate the weeks delayed using the formulas =IF(Status@row <> "Complete", IF([Days delayed]@row < 0, ROUNDUP(-[Days delayed]@row / 7), "--"), "--") - however, i get the error message invalid operations.

example below

Additonally, i am trying to use the weeks delayed formula to return a delayed status of Not started, On Schedule, Minor Delay or Major Delay using the following formula : =IF(Status@row = "complete", "Complete", IF(AND(Status@row = "not started", [Weeks_delay]@row = "--"), "Not Started",IF(AND(Status@row = "in progress", [Weeks_delay]@row = 0), "On Schedule", IF(AND(OR(Status@row = "not started", Status@row = "in progress"), [Weeks_delay]@row > 0, [Weeks_delay]@row < 5), "Minor Delay", IF(AND(OR(Status@row = "not started", Status@row = "in progress"), [Weeks_delay]@row > 4), "Major Delay")))))

This is also returning an Invaid operations error.

i know thats due to weeks delayed being incorrect but is there anything else wrong with the formula itself?

mla itself?

Genevieve P.

Hi @Khanambano

I'd be happy to help!

For your first formula, I believe you may want to swap around a few things. Right now you're checking to see if the Days Delayed is less than 0, meaning negative days. It sounds like you're actually looking to see if the days delayed is greater than 0.

I also notice a negative symbol in your ROUNDUP function. Are you wanting this to display a negative number? I've assumed you want a positive, to show how many weeks this is delayed.

Try:

=IF(Status@row <> "Complete", IF([Days Delayed]@row > 0, ROUNDUP([Days Delayed]@row / 7), "--"), "--")

For your second formula, it looks good! If we resolve the first formula then the second should be fine.

One thing to note: in both of your formulas you are looking for the word "Complete". However, it looks like the cell has "Completed" with a D at the end. Make sure the text you're searching for in your formula is the exact same as the values in that Status column.

Let me know if you're still getting an error and I'd be happy to troubleshoot further!

Khanambano

Thank you @Genevieve P. .

The Weeks Delayed definitely worked better with the adjustments you suggested but there is still something off with the logic.

in the example below - the days delayed should return NetworkDays between 12/16/22 and 2/01/2022 but its returning blank. the formula curently there is

=IF(AND([Actual Finish]@row = "", Status@row = "Completed"), 0, IF(Status@row <> "Completed", "", IF([Actual Finish]@row > Finish@row, NETWORKDAYS(Finish@row, [Actual Finish]@row), 0)))

I think the whatever the issue in the days delayed formula is also affecting weeks delayed which should 8 weeks and the delay status then should be Major delayed since anything above 4 weeks is Major delay.

There must be something wrong with the last IF statment, would you be able to review?

Genevieve P.

Hi @Khanambano

This is because your row is currently "In Progress", so it's returning a blank cell:

=IF(AND([Actual Finish]@row = "", Status@row = "Completed"), 0, IF(Status@row <> "Completed", "", IF([Actual Finish]@row > Finish@row, NETWORKDAYS(Finish@row, [Actual Finish]@row), 0)))

Is that incorrect?

Khanambano

@Genevieve P.

Status column is in Progress because the 2/1/23 date has not been achieved. the PM is projecting the actual finish to be 2/1/23 and the project is only 45% complete. when the percent complete changes to 100% the status will then change to Completed.

Genevieve P.

Hi @Khanambano

It sounds like you just need to remove that statement from the formula then, since you want to calculate the Networkdays even if the task is currently In Progress.

Here's what it would look like removed:

=IF(AND([Actual Finish]@row = "", Status@row = "Completed"), 0, IF([Actual Finish]@row > Finish@row, NETWORKDAYS(Finish@row, [Actual Finish]@row), 0))

Khanambano

@Genevieve P. Thank you so much for your help on my previous question. and Happy New Year

Quick question. if this following formula brings back a numeric value how can i expand the equation to return a percentage of a total. I tried dividing the entire formula by the total row but that didn't work. Any advice. i want the chart below to return percentages. for example, the first row the '3' should say 1 - which i would then convert to percentage and it would reflect 100.

=COUNTIF({S&0- Transformation PMO Range 5}, [Column7]30)

Genevieve P.

Hi @Khanambano

How are you getting the Total at the end? If you're adding together the cells in that row you'll receive a circular reference.

What I would do here is add all the other cells together as the "Total" within this same formula, like so:

=COUNTIF({Range 1}, [Column8]$30) / SUM(COUNTIF({Range 1}, $[Column4]$30), COUNTIF({Range 1}, $[Column5]$30), COUNTIF({Range 1}, $[Column7]$30), COUNTIF({Range 1}, $[Column6]$30), COUNTIF({Range 1}, $[Column8]$30))

If you lock the cell reference using $ signs in front of the column name and the cell location then you can easily drag this across the row to update all references:

$[Column4]$30

I hope that helps!

Cheers,

Genevieve