Embedded IF formula in NETDAYS
I am attempting to write a NETDAYS formula dependent on the input from a column on a sheet. I imagine I need to embed an IF formula, but I have had no luck in the format.
Any help would be much appreciated.
Best Answers
-
Christian Graf ✭✭✭✭✭
Okay,
I don't know what date columns you are using, but the following formula should get you close:
=IF([Type of Incident]@row = "Accident", NETDAYS([date 1]@row, [date 2]@row),"")
Answers
-
Christian Graf ✭✭✭✭✭
Hello@Jennifer Parins
Could you be more specific? Do you have images of what you are trying to accomplish?
Nested If formulas could be what you are looking for. Here is an example of a formula that checks a column for a value and performs a netdays formula based on what it finds.
=IF(column1@row = "Start", NETDAYS(date1@row, date2@row), IF(column1@row = "End", NETDAYS(Today(), date2@row),""))
If you share some images of what values you want found, and describe what you want to happen, I could build you an exact formula for your application.
Hope this helps!
-
I am attempting to find the NETDAYS only when the indicated column is "Accident".
I will look back at your formula and see if I can figure out where my formula went wrong.
Thanks for your help!!!
-
Christian Graf ✭✭✭✭✭
Okay,
I don't know what date columns you are using, but the following formula should get you close:
=IF([Type of Incident]@row = "Accident", NETDAYS([date 1]@row, [date 2]@row),"")
-
I am still struggling with this equations. I feel that I am not explaining my needs very well.
When the "Type of Incident" column registers and accident, I want to find the number of days from today to that last accident.
This is the formula I have and I hope someone will be able to tell me where it is not correct:
-
Genevieve P. Employee Admin
Christian's formula above would be the correct structure for anin-sheetformula, but it looks like you're wanting to calculate thisacross sheets.
The issue here is that the range {Type of Incident} is looking at your entire column, versus looking at one cell. How will your formula know what row to look at?
If you just want to check and see ifanycell contains "Accident", then you could use a COUNTIF:
=IF(COUNTIF({Type of Incident}, "Accident") > 0, NETDAYS(MAX({DATE}), TODAY()))
Keep in mind that if your Max Date in that sheet is not the same as one of your rows with "Accident" then this won't bring back the correct row's data.
I would suggest creating a formula on the main source sheet, so you can calculate the NETDAYS for each individual row, then you could pull this into a Report instead of a second sheet.
Cheers,
Genevieve
Help Article Resources
Categories
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//www.santa-greenland.com/community/discussion/comment/328162/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/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":[]},"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":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":41,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+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-08-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"