Nested IF/AND
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.
Best Answer
-
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))))
Answers
-
Todd M Keller ✭✭✭✭
我可能忽略了一点,但它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 KellerI 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 HoydicI'm happy to hear you finally got it working......
Help Article Resources
Categories
Check out theFormula Handbook template!
Your second IF criteria states that IF [OD Start Date]@row = \"True\". The problem with this is that [OD Start Date] column is not a True\/False column such as a checkbox or flag. It's a date column, so it will never be True or False.<\/p>
Your final IF criteria states \"[NEW Hire Request for Contract Date]@row = Date\" would also cause an error. First it's surrounded by \"//www.santa-greenland.com/community/discussion/111260/\" which means everything in-between should be treated as a string of text. It's not a criteria statement that will be evaluated. Second there's no \"= Date\" functionality that will tell you the value is a valid date. If you have that column set as a Date column that is restricted to dates only, then any entry will be a valid Date otherwise the data wouldn't have been accepted into the cell.<\/p>
Try this in your [NEW Hire Request for Contract Date] column: =IF(AND(NOT(ISBLANK([OD Start Date]@row)), [Hire Type]@row = \"New Hire\"), [OD Start Date]@row - 14, \"//www.santa-greenland.com/community/discussion/111260/\")<\/p>
The [NEW Hire Request for Contract Date] column must be a Date type column for this to work.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":["discussion","question"]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":112447,"type":"question","name":"Formula to check whether or not a cell has been hyperlinked","excerpt":"I am trying to create a helper column that checks whether a cell in a nearby column (\"Project\" in this case) has been hyperlinked or not. I want to return 1 if True or 0 if False. Something like this: =IF(CONTAINS(\"http\", Project@row), 1, 0) However, when I hyperlink using the menu dropdown (see attached image), this…","snippet":"I am trying to create a helper column that checks whether a cell in a nearby column (\"Project\" in this case) has been hyperlinked or not. I want to return 1 if True or 0 if False.…","categoryID":322,"dateInserted":"2023-11-01T17:19:23+00:00","dateUpdated":null,"dateLastComment":"2023-11-01T18:48:07+00:00","insertUserID":48211,"insertUser":{"userID":48211,"name":"michelle.lewis-miller","url":"https:\/\/community.smartsheet.com\/profile\/michelle.lewis-miller","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZQNWGRXAb3o!3FVUfwHciYQ!Pc3Zb-an1Ae","dateLastActive":"2023-11-01T18:56:16+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":142964,"lastUser":{"userID":142964,"name":"Ray Lindstrom","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ray%20Lindstrom","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/5Y0KP6UKIBTU\/nY3615V0DPRZV.jpg","dateLastActive":"2023-11-01T18:58:16+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":20,"score":null,"hot":3397724250,"url":"https:\/\/community.smartsheet.com\/discussion\/112447\/formula-to-check-whether-or-not-a-cell-has-been-hyperlinked","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112447\/formula-to-check-whether-or-not-a-cell-has-been-hyperlinked","format":"Rich","tagIDs":[254,331],"lastPost":{"discussionID":112447,"commentID":402628,"name":"Re: Formula to check whether or not a cell has been hyperlinked","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402628#Comment_402628","dateInserted":"2023-11-01T18:48:07+00:00","insertUserID":142964,"insertUser":{"userID":142964,"name":"Ray Lindstrom","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ray%20Lindstrom","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/5Y0KP6UKIBTU\/nY3615V0DPRZV.jpg","dateLastActive":"2023-11-01T18:58:16+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/CAS3JBR51VFG\/smartsheet-menuhyperlink.jpg","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/CAS3JBR51VFG\/smartsheet-menuhyperlink.jpg","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/CAS3JBR51VFG\/smartsheet-menuhyperlink.jpg","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/CAS3JBR51VFG\/smartsheet-menuhyperlink.jpg","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/CAS3JBR51VFG\/smartsheet-menuhyperlink.jpg","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/CAS3JBR51VFG\/smartsheet-menuhyperlink.jpg"},"alt":"smartsheet_menuhyperlink.jpg"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-11-01T18:51:58+00:00","dateAnswered":"2023-11-01T18:48:07+00:00","acceptedAnswers":[{"commentID":402628,"body":"