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

Options
✭✭

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?

Tags:

• ✭✭✭✭
Options

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.

Ward Hively

President and Chief Consultant

🌉Skyway Consulting Co.🌉

Need Smartsheet Help? Schedule a complementary consultation click SCHEDULE

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

ArcGIS Online

✅ Arc Py

✅ Manage your GIS records from Dynamic View, Sheet or Report

✅ No Code (Limited Applications Apply)

Website: https://skywayconsultingco.com

• ✭✭✭✭✭✭
edited 05/15/23
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!