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
-
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
-
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!!!
-
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!
-
ok then, forget i said anything. Bye
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 443 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!