Nested IF/AND
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
-
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))
-
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).
-
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))
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!