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
    Linda Hoydic ✭✭✭
    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))))

Answers

  • Todd M Keller
    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),"")))

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

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

  • Todd M Keller
    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")

  • @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
    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
    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
    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
    Linda Hoydic ✭✭✭
    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))))

  • Todd M Keller
    Todd M Keller ✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!
You're getting a circular error because you're trying to use the value from your \"Hire Type\" column to calculate a value for your \"Hire Type\" column. That formula will always be chasing it's tail.<\/p>

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

Hi @michelle.lewis-miller<\/a>,<\/p>

Unfortunately there is no formula or other functionality native in Smartsheet to determine if a cell contains a hyperlink. <\/p>

It would make for a good Product Idea. Feel free to submit one here https:\/\/community.smartsheet.com\/categories\/smartsheet-product-feedback-&-ideas?sort=top<\/a> unless you see another one already submitted for the same reason that you can up-vote instead.<\/p>

BRgds,<\/p>

-Ray<\/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":[{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":331,"urlcode":"hyperlink","name":"hyperlink"}]},{"discussionID":112403,"type":"question","name":"Number of days between today and a specific date","excerpt":"I have a date of contact in one column and in another need to show the number of days since that date of contact. I'm using the following formula: =NETDAYS([Last Contact]@row, TODAY()) The problem is that it is showing an extra day for some reason. If the date of contact was 10\/30\/23 and today is 10\/31\/23, the formula…","snippet":"I have a date of contact in one column and in another need to show the number of days since that date of contact. I'm using the following formula: =NETDAYS([Last Contact]@row,…","categoryID":322,"dateInserted":"2023-10-31T19:44:25+00:00","dateUpdated":null,"dateLastComment":"2023-11-01T17:37:59+00:00","insertUserID":169343,"insertUser":{"userID":169343,"name":"atwolfindy","url":"https:\/\/community.smartsheet.com\/profile\/atwolfindy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-11-01T17:37:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":169343,"lastUser":{"userID":169343,"name":"atwolfindy","url":"https:\/\/community.smartsheet.com\/profile\/atwolfindy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-11-01T17:37:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":27,"score":null,"hot":3397642944,"url":"https:\/\/community.smartsheet.com\/discussion\/112403\/number-of-days-between-today-and-a-specific-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112403\/number-of-days-between-today-and-a-specific-date","format":"Rich","lastPost":{"discussionID":112403,"commentID":402596,"name":"Re: Number of days between today and a specific date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402596#Comment_402596","dateInserted":"2023-11-01T17:37:59+00:00","insertUserID":169343,"insertUser":{"userID":169343,"name":"atwolfindy","url":"https:\/\/community.smartsheet.com\/profile\/atwolfindy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-11-01T17:37:32+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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-11-01T21:45:37+00:00","dateAnswered":"2023-10-31T20:32:16+00:00","acceptedAnswers":[{"commentID":402477,"body":"

Try subtracting one from the output.<\/p>

=NETDAYS([Last Contact]@row, TODAY()) - 1<\/strong><\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions