Networking days taken from a start date and then one of two columns

I'm looking to find the average working days from the start date to the date of the job's first review. The first review could happen in two different ways represented in two different columns, a work in progress review, or an official review. Depending on the complexity of the job they may or may not need a WIP review.

I understand how to find the networkings days, but I want to be able to use the wip review date and if one isn't there use the official review date instead to give me the networkings days

Example of the sheet would look like this:

Is there a formula that could do this automatically?

Answers

  • BullandKhmer
    BullandKhmer ✭✭✭✭✭

    Just build a helper column

    =IF([WIP Review]@row<>"",[Offcial Review]@row,[WIP Review]@row)


    The use the helper column as part of your network days formula

  • @BullandKhmer

    Thanks for the response! I put a helper column in and this is only bringing me back the official review date, and only for the lines that have a WIP review. What I'd need it to do is bring up the WIP review date, and then if there is no WIP review date, bring up the Official review date. Maybe if could be a formula with timing, cause the WIP review will always be before the official review? I don't know!

    Thank you!!!

  • BullandKhmer
    BullandKhmer ✭✭✭✭✭

    Look Im sorry, I gave u the concept, u do the details. :)

  • Hey @BullandKhmer interesting response within the community boards.

    You gave me a concept of building a helping column with a formula and then use that helper column with the networking days which is a great idea! I built the helper column in my sheet but the formula you gave it didn't give me one or the other. I need help creating a formula to bring me one date or the other depending on which is first, and then I can create the networking days column. Why even give a formula if it wasn't going to be what I needed?

    What a confusing response - Hopefully someone else can help!

  • BullandKhmer
    BullandKhmer ✭✭✭✭✭

    ok then, forget i said anything. Bye

  • BullandKhmer
    BullandKhmer ✭✭✭✭✭
    edited 04/30/23

    And you are welcome by the way.

    Ive actually gone back and realized the mistake. Took a couple of seconds, you should be able to figure it out yourself.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Nicolette Marinos

    I'd be happy to help explain 🙂

    You can actually use an IF statement right in your NETWORKDAYS formula so you don't need an additional helper column.

    To do so, we'll use the previous IF formula. We'll want to change it to say that if the WIP is blank, instead of is not blank (which I believe is the mistake referred to - an easy mistake to make!):

    IF([WIP Review]@row = "", [Official Review]@row, [WIP Review]@row)


    Then we can place this IF statement directly into a NETWORKDAYS function, like so:

    =NETWORKDAYS([Start Date]@row, IF([WIP Review]@row = "", [Official Review]@row, [WIP Review]@row))


    Let me know if this makes sense and works for you!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!