Stop counting months when a completion date has been met
Hello,
I need to have a column that calculates time in a program from a start date freeze when the employee's status changes, it needs to keep counting as long as the employee is still "Active" in the program. I'm working with "Status Code,", "Enrollment Date," and "Date Licensed/Dropped" columns.
I have this column set up in a google sheet now but want to move over to Smartsheet. My current formula looks like this:
=if([Status Code]="Dropped",([Date Licensed/Dropped]-[Enrollment Date]/7/4,if([Status Code]="Graduated",([Date Licensed/Dropped]-[Enrollment Date]/7/4,datedif([Enrollment Date]-today(),"d")/7/4))
I know that I can't use the datedif formula in smartsheet so i'm not really sure how to make this go...
Comments
-
J. Craig Williams ✭✭✭✭✭✭I don't know what the "/7/4" portion of the Google Sheet formula does.
It looks like if either Dropped or Graduated, then you take the difference between the two dates.
Try this:
=IF(OR([Status Code]@row= "Dropped", [Status Code]@row= "Graduated"),[Date Licensed/Dropped]@row-[Enrollment Date]@row, [Enrollment Date]@row- TODAY())
Smartsheet needs both column and row to reference the cell.@rowis "this row". You can replace with the row number if you are referencing a different row than the one the formula is on.
I hope this helps. I did not test it.
Craig
Help Article Resources
Categories
=IF(CONTAINS(\"In Progress\", CHILDREN()), \"In Progress\", IF(COUNTIF(CHILDREN(), \"Complete\") = COUNT(CHILDREN([Primary Column]@row)), \"Complete\", \"Not Started\"))<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107794,"type":"question","name":"How to COUNT multi-select drop down list.","excerpt":"My sheet is for process improvement ideas. When one is finished, we have a column where we can check off which areas we improved (some are just Cost Savings, some have 3 or 4 options selected). I'm trying to count how many times each improvement category is checked off on the entire sheet. Note: I have 5 categories, for…","snippet":"My sheet is for process improvement ideas. When one is finished, we have a column where we can check off which areas we improved (some are just Cost Savings, some have 3 or 4…","categoryID":322,"dateInserted":"2023-07-19T13:53:42+00:00","dateUpdated":null,"dateLastComment":"2023-07-19T14:13:30+00:00","insertUserID":148579,"insertUser":{"userID":148579,"name":"bryanl","title":"","url":"https:\/\/community.smartsheet.com\/profile\/bryanl","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!!Z8_P2fycHH0!w6dOv1SnhD0!hJJk1uz8qPR","dateLastActive":"2023-07-19T14:08:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":148579,"lastUser":{"userID":148579,"name":"bryanl","title":"","url":"https:\/\/community.smartsheet.com\/profile\/bryanl","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!!Z8_P2fycHH0!w6dOv1SnhD0!hJJk1uz8qPR","dateLastActive":"2023-07-19T14:08:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":29,"score":null,"hot":3379552032,"url":"https:\/\/community.smartsheet.com\/discussion\/107794\/how-to-count-multi-select-drop-down-list","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107794\/how-to-count-multi-select-drop-down-list","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107794,"commentID":385922,"name":"Re: How to COUNT multi-select drop down list.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385922#Comment_385922","dateInserted":"2023-07-19T14:13:30+00:00","insertUserID":148579,"insertUser":{"userID":148579,"name":"bryanl","title":"","url":"https:\/\/community.smartsheet.com\/profile\/bryanl","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!!Z8_P2fycHH0!w6dOv1SnhD0!hJJk1uz8qPR","dateLastActive":"2023-07-19T14:08:27+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-07-19T14:12:58+00:00","dateAnswered":"2023-07-19T14:08:08+00:00","acceptedAnswers":[{"commentID":385920,"body":"
If I understand correctly:<\/p>
You have a multi select dropdown where you specify which area where improved within each improvement idea.<\/p>
If that is correct, your first formula should work, although it would be better practice to use the HAS function for this setup, see below: <\/p>
=COUNTIF({Areas Improved}, HAS(@cell,\"Customer Experience\"))<\/p>
<\/p>
Check your cross sheet reference {Areas Improved} <\/strong>to make sure it is setup correctly<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107790,"type":"question","name":"Adding an AND into a formula","excerpt":"Good morning, Community! Current formula (which is working): =IF(CONTAINS(\"APP\", [Employment Type]@row), JOIN([Dropdown Selection]@row:[Employee Status]@row, \"; \")) However, I need to include if the Employee Status equals Active within the above and not sure on the syntax?","snippet":"Good morning, Community! Current formula (which is working): =IF(CONTAINS(\"APP\", [Employment Type]@row), JOIN([Dropdown Selection]@row:[Employee Status]@row, \"; \")) However, I…","categoryID":322,"dateInserted":"2023-07-19T13:21:49+00:00","dateUpdated":null,"dateLastComment":"2023-07-19T14:13:56+00:00","insertUserID":163131,"insertUser":{"userID":163131,"name":"cghallo_UCDenver","title":"Smartsheet Consultant","url":"https:\/\/community.smartsheet.com\/profile\/cghallo_UCDenver","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-19T14:16:10+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-19T17:59:47+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":28,"score":null,"hot":3379551345,"url":"https:\/\/community.smartsheet.com\/discussion\/107790\/adding-an-and-into-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107790\/adding-an-and-into-a-formula","format":"Rich","lastPost":{"discussionID":107790,"commentID":385923,"name":"Re: Adding an AND into a formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385923#Comment_385923","dateInserted":"2023-07-19T14:13:56+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-19T17:59:47+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\/R71AMNDX813P\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-19T13:49:51+00:00","dateAnswered":"2023-07-19T13:46:29+00:00","acceptedAnswers":[{"commentID":385910,"body":"
