Nested IF/AND

Options

I have two formulas that work independently as they should, but I need to combine them into one and count NETDAYS, if a certain department, and count NETWORKDAYS for all other departments. I was able to combine without error, but it doesn't calculate properly, it will work for one but not the other.

Formula 1 - is the one we have been using and it works fine to calculate the working days.

=IF(AND([Department Head approval]@row ="Approved", [Cancel Request]@row = "Y"), -NETWORKDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, IF([Department Head approval]@row ="Approved", NETWORKDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, 0))

Formula 2 - NetDays for Department Cyber - SOC.

=IF(AND(Department@row = "Cyber - SOC",[Department Head approval]@row = "Approved", [Cancel Request]@row = "Y"), -NETDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, IF([Department Head approval]@row = "Approved", NETDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, 0))

I've tried everything I can think of. I'm sure it's something simple that I'm missing. Any help would be greatly appreciated.

Answers

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    Options

    Try this:

    =IF(AND(Department@row = "Cyber - SOC", [Department Head approval]@row ="Approved", [Cancel Request]@row = "Y"), -NETWORKDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, IF(AND([Department Head approval]@row = "Approved", [Cancel Request]@row = "Y"), -NETWORKDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, 0))

  • Linda Hoydic
    Linda Hoydic ✭✭✭✭
    Options

    Thank you, I will try this, but I don't think this is what I need to accomplish. If Department is Cyber SOC, I need the formula to count NETDAYS (7 days/week), if not SOC it needs to count NETWORKDAYS (business days). I don't see where this will count NETDAYS or NETWORKDAYS. Further, within each formula, I need to account for the cancel request to subtract time if not canceled count time, based on the days to count (NET or NETWORK).

  • Linda Hoydic
    Linda Hoydic ✭✭✭✭
    edited 10/04/23
    Options

    UPDATE: I've been able to create two new formulas that will count the days either NETDAYS or NETWORKDAYS based on Department = Cyber - SOC. If Cancel Request is Y, is subtracts based on NETDAYS or NETWORKDAYS. However, when I try to combine the two, it gives me the Incorrect Argument Set error, I can't figure out what I'm missing or if the order is wrong (I've tried that, too).

    Formula 1 will subtract the days as needed -

    =IF(AND(Department@row = "Cyber -SOC", [Cancel Request]@row = "Y"), -NETDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, -NETWORKDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row)

    Formula 2 counts the days as needed -

    =IF(AND(Department@row = "Cyber -SOC", [Department Head approval]@row = "Approved"), NETDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, NETWORKDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row)

    Combined -

    =IF(AND(Department@row = "Cyber - SOC", [Cancel Request]@row = "Y"), -NETDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, -NETWORKDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, IF(AND(Department@row = "Cyber - SOC", [Department Head approval]@row = "Approved"), NETDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, NETWORKDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row))

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Linda Hoydic

    For combining the formulas:

    • The first thing to do is check the Department@row for "Cyber - SOC" as a separate IF.

    =IF(Department@row = "Cyber -SOC", 

    • If that's correct, then you can do a second IF statement to see if Cancel Request = Y (and do your calculation)

    =IF(Department@row = "Cyber -SOC", IF([Cancel Request]@row = "Y", -NETDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row ...

    • But if Cancel Request is not Y then we move to check if the Approval is "Approved" (and do that other calculation).

     ...IF([Department Head approval]@row = "Approved", NETDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row

    • Otherwise, if none of those criteria are met, finish with your same end statement.

    ...NETWORKDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row)


    So something like this:

    =IF(Department@row = "Cyber -SOC", IF([Cancel Request]@row = "Y", -NETDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, IF([Department Head approval]@row = "Approved", NETDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, NETWORKDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row)))


    Let us know if this works for you!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!