NETWORKDAYS/IF/ISBLANK - Can you subtract from the returned day count number?

Whit.Yas

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!!

Ward.Hively

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.

Danielle Arteaga

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.