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?

Find more posts tagged with

Formulas

Project Management

Accepted answers

All comments

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

Nicolette Marinos

@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

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

Nicolette Marinos

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

ok then, forget i said anything. Bye

BullandKhmer

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.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.