Calculated Days Delayed. weeks delayed

Khanambano
edited 11/04/22 in Formulas and Functions

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.



Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 11/22/22 Answer ✓

    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

Answers

  • Christian G.
    Christian G. ✭✭✭✭✭✭
    edited 11/02/22

    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.
    Genevieve P. Employee Admin
    edited 11/22/22 Answer ✓

    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

  • 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.
    Genevieve P. Employee Admin
    edited 11/22/22

    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)))

  • Hi @Genevieve P.

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


  • Genevieve P.
    Genevieve P. Employee Admin

    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!

  • 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.
    Genevieve P. Employee Admin

    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!

  • 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.
    Genevieve P. Employee Admin

    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?

  • @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.
    Genevieve P. Employee Admin

    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))

  • @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.
    Genevieve P. Employee Admin

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!