Nested IF/AND

Linda Hoydic

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.

Ray Lindstrom

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

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

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.

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