Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Nested IF/AND

    ✭✭✭✭
    in Formulas and Functions

    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.

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • ✭✭✭✭
      Answer ✓

      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))))

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭

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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      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.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      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")

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      @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))))

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      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.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      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,

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      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....

      · Share on FacebookShare on Twitter
    • ✭✭✭✭
      Answer ✓

      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))))

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

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

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions