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?


Thanks in advance for any advice!!

Tags:

Answers

  • Ward.Hively
    Ward.Hively ✭✭✭✭
    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

    ✅ Smartsheet Advanced

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

    ✅ No Code (Limited Applications Apply)

    Website: https://skywayconsultingco.com

    LinkedIn: (49) Ward Hively | LinkedIn

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    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!