Network Days + If statements
Hello,
I'm trying to build a formula that will show the network days between a [Start Date] and an [Completion Date], but also 1) if [Start Date] is blank, then return a blank instead of an #Invalid Data Type error; and also 2) if [Completion Date] is Blank, the formula subtracts the [Start Date] from Today(), so that we have a running tally of network days during which the project is open (instead of blank), which then stops counting once [Completion Date] is entered.
I can get the components to work individually but I can't seem to work out the logic with the IF statements. I have also tried with IFERROR but also can't seem to work it out.
=IF(BLANK([Start Date]@row, ""), IF(Blank([Completion Date]@Row, NETWORKDAYS([Start Date]@row, TODAY()), NETWORKDAYS([Start Date]@row, [Completion Date]@row)))
Thank you in advance!
Best Answer
-
Hey @Blake T
Sorry, I didn't check your parentheses.
=IF(ISBLANK([Start Date]@row), "", IF(ISBLANK([Completion Date]@row), NETWORKDAYS([Start Date]@row, TODAY()), NETWORKDAYS([Start Date]@row, [Completion Date]@row)))
Will this work for you?
Kelly
Answers
-
Hey @Blake T
The function you are looking for is ISBLANK
=IF(ISBLANK([Start Date]@row, ""), IF(ISBLANK([Completion Date]@Row, NETWORKDAYS([Start Date]@row, TODAY()), NETWORKDAYS([Start Date]@row, [Completion Date]@row)))
Will this work for you?
Kelly
-
Thank you so much @Kelly Moore , that was quite the oversight on my part. Unfortunately that correction alone wasn't enough. I have been tweaking my parentheses but do not seem to be able to solve the #Imparseable error.
=IF(ISBLANK([Start Date]@row, ""), IF(ISBLANK([Completion Date]@row, NETWORKDAYS([Start Date]@row, TODAY()), NETWORKDAYS([Start Date]@row, [Completion Date]@row))
-
Hey @Blake T
Sorry, I didn't check your parentheses.
=IF(ISBLANK([Start Date]@row), "", IF(ISBLANK([Completion Date]@row), NETWORKDAYS([Start Date]@row, TODAY()), NETWORKDAYS([Start Date]@row, [Completion Date]@row)))
Will this work for you?
Kelly
-
Ahhh, that worked! I can't thank you enough!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!