Nested IF/AND

Linda Hoydic

I asked a question about this the other day, when I thought I had my formulas working, but as I continued to try to resolve the issue, I found that I didn't have the correct individual formulas. So, I'm posing the question again, providing the correct individual formulas (I hope). Individually, the formulas work and count the days correctly. It's when I try to put them together, I get errors. I've tired adding OR into the equation, but I'm not sure where I would place it or revise the formula.

I also tried using NETWORKDAYS as the result if false, but it counted the days even if Department Head approval was blank, so that's not right either. I need to be sure it uses Department Head Approval = Approved to count days.

IF Department = Cyber SOC, the days need to count NETDAYS; IF Department <> Cyber SOC, the days need to count NETWORKDAYS.

Formula 1 - =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))

Formula 2 - =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([Department Head approval]@row="Approved", NETWORKDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, 0))

I'm completely stuck now and can't seem to figure out how to pull this together. I really hope there is a way. Any information or idea is very much appreciated.

Linda Hoydic

Just to close the loop - I finally got it to work as I need it to. I think it was the order. Here is the final working formula string.

=IF(AND(Department@row = "Cyber - SOC", [Cancel Request]@row = "Y"), -NETDAYS([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, IF(AND(Department@row <> "Cyber - SOC", [Cancel Request]@row = "Y"), -NETWORKDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, IF(AND(Department@row <> "Cyber - SOC", [Department Head approval]@row = "Approved"), NETWORKDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, 0))))

Todd M Keller

I may be missing the point here, but it seems to me both of these formula's will always count the days if [Department Head Approval]@row = "Approved", the tests for {Department] and [Cancel Request] seem unnecessary in the examples above. If your AND function returns false, you are then just testing for [Department Head Approval] and proceeding to count the days.

What happens when you try this:

=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(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),"")))

Linda Hoydic

I need to count the days if approved and subtract the number if cancel. I don't think this will subtract (show -1) in the count if cancel is Y.

Linda Hoydic

@Todd M Keller I tried your formula and it returned #UNPARSEABLE. Similar to my issue....Thanks for trying.

Todd M Keller

I tested this, and it should work. All my columns were Text except the two date columns.

=IFERROR(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(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, "")), "Houston, we have a problem")

Linda Hoydic

@Todd M Keller - I will try the one you tested, but I still think it is missing something - if Cancel Request = Y, I need it to subtract the count. That said, I revised the formula and did not get an error, however, it seems to be ignoring the section I have bolded here. I can get it to calculate the correct number of days based on Approved and department; if the department is NOT Cyber SOC, it will subtract the days if Cancel = Y; but when the department IS Cyber SOC and Cancel = Y, it WILL NOT subtract. It's like the formula skips this section, because it will subtract days but using NETWORKDAYS, not NETDAYS. Can you see what I may be missing? I tried the two Cancel Request statements together and that didn't work either. For example, if Cyber SOC requests 10/26 -10/31, it should count 6 days, not Cyber SOC would be 4. This calculation works fine, but if I set Cancel to Y, it only subtracts 4 regardless of department.

=IF(AND(Department@row <> "Cyber-SOC", [Cancel Request]@row = "Y"), -NETWORKDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, IF(AND(Department@row <> "Cyber - SOC", ([Department Head approval]@row = "Approved")), NETWORKDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, IF(AND(Department@row = "Cyber - SOC", [Cancel Request]@row = "Y"), -NETDAYS([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, 0))))

Todd M Keller

Alright.......

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

That works, however, the way this flows, if the column [Department Head Approval]@row is populated with "Approved", then your value will always be positive. You'd have to delete the approval for it to acknowledge the cancellation.

Todd M Keller

On a side note, I'd never used a "-" directly in front of a function like that before. I eliminated it because I thought it was a typo. I would usually use the Sum function, but clearly that isn't necessary here. I hope that helps.

Have a good night,

Todd M Keller

One other thought, if the flow causes a problem, the statement order could be flipped around so that if it is cancelled after approval, it would evaluate that first.......

Bon Chance....

Linda Hoydic

Just to close the loop - I finally got it to work as I need it to. I think it was the order. Here is the final working formula string.

=IF(AND(Department@row = "Cyber - SOC", [Cancel Request]@row = "Y"), -NETDAYS([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, IF(AND(Department@row <> "Cyber - SOC", [Cancel Request]@row = "Y"), -NETWORKDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, IF(AND(Department@row <> "Cyber - SOC", [Department Head approval]@row = "Approved"), NETWORKDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, 0))))

Todd M Keller

@Linda Hoydic I'm happy to hear you finally got it working......