NETWORKDAYS/IF/ISBLANK - Can you subtract from the returned day count number?
Hello!
I am having a hard time figuring out a formula that will count the NETWORKDAYS between a [Start Date]51 and the end date [Escalation Completed Date]29 that will count as TODAY() if the end date cell [Escalation Completed Date]29 is blank AND if the [Days On-Hold]29 is NOT blank subtract the number in that cell from the number returned with my original formula.
This is my current functioning formula:
=NETWORKDAYS([Start Date]51, IF(ISBLANK([Escalation Completed Date]29), TODAY(), [Escalation Completed Date]29))
Is there a Function for subtracting in this way?
Thanks in advance for any advice!!
Answers
-
Hello Whit.Yas,
Let's make some assumptions first,
-If [Days On-Hold]29 is not blank then it IS text (days).
-So we would say IF [Escalation Completed Date]29 is blank and [Days On-Hold]29 is text then we want to subtract [Days On-Hold]29 from NETWORKDAYS([Start Date]51, IF(ISBLANK([Escalation Completed Date]29), TODAY(), [Escalation Completed Date]29))
Therefore
=NETWORKDAYS([Start Date]51, IF(ISBLANK([Escalation Completed Date]29), TODAY(), [Escalation Completed Date]29))-IF(ISTEXT([Days On-Hold]29),[Days On-Hold]29,0)
Try that and let me know!
Ward Hively
Skyway Consulting Co.
CEO | Skyway Consulting Co.
Does your Dashboard need a map that updates from Smartsheet Data?
We pioneered 101+ ways to add a map to a Smartsheet Dashboard.
Smartsheet and GIS Integrations
→ Explore Smartsheet Maps (ArcGIS)
→ LinkedIn
-
I hope I'm following your logic here …
My suggestion is to add a column to your sheet that calculates the Escalation Completed Date as TODAY() if the Escalation Completed Date is blank. Here's an example:
In the [Escalation Date Helper] column, I put this formula:
=IF(ISBLANK([Escalation Completed Date]@row), TODAY())
In the [Formula Result] column, I put this formula:
=IF(ISBLANK([Escalation Completed Date]@row), NETWORKDAYS([Start Date]@row, [Escalation Date Helper]@row), NETWORKDAYS([Start Date]@row, [Escalation Completed Date]@row) - [Days on Hold]@row)
If [Escalation Completed Date] is blank, the formula calculates the number of weekdays between [Start Date] and [Escalation Date Helper] for each row.
If [Escalation Completed Date is NOT blank, the formula calculates the weekdays between the [Start Date] and [Escalation Completed Date] values MINUS the [Days on Hold] value. You don't have to worry about whether or not that cell is blank, because Smartsheet will assume a value of zero for any blank cell.
You can hide the [Escalation Date Helper] column to avoid any confusion for people reading the raw data.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 138 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!