Years in Service Formula

RDCUB
RDCUB ✭✭
edited 12/09/19 inFormulas and Functions

Good Day,

We are struggling with getting a "Years in service" calculator to work, after having pulled all the formulas that could be found by previous posts, still no luck.

=IF(AND(MONTH([Start Date]1) >= MONTH(TODAY()), DAY([Start Date]1) > DAY(TODAY())), YEAR(TODAY()) - 1 - YEAR([Start Date]1), YEAR(TODAY()) - YEAR([Start Date]1))

This is a copy and paste from another post, which it appeared had worked for them.

Dates are formatted as 8/2/2017, have tried 08/02/2017

But I consistently get #INVALIDDATATYPE

Any suggestions would be greatly appreciated.



Robert

Tags:

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Try removing one of the closing parenthesis after the DATE function.<\/p>"},{"commentID":388644,"body":"

Thanks! That sorted it!😀<\/span><\/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":108438,"type":"question","name":"NETWORKDAYS WITH AN IF??","excerpt":"Can anyone help with a formula to track the number of days between 2 separate columns with the IF of stopping the count once a date is populated in one of those columns?","snippet":"Can anyone help with a formula to track the number of days between 2 separate columns with the IF of stopping the count once a date is populated in one of those columns?","categoryID":322,"dateInserted":"2023-08-02T19:37:00+00:00","dateUpdated":null,"dateLastComment":"2023-08-02T20:11:28+00:00","insertUserID":164426,"insertUser":{"userID":164426,"name":"JT_22002","url":"https:\/\/community.smartsheet.com\/profile\/JT_22002","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-03T11:32:53+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-03T12:32:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":18,"score":null,"hot":3382012708,"url":"https:\/\/community.smartsheet.com\/discussion\/108438\/networkdays-with-an-if","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108438\/networkdays-with-an-if","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108438,"commentID":388600,"name":"Re: NETWORKDAYS WITH AN IF??","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388600#Comment_388600","dateInserted":"2023-08-02T20:11:28+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-03T12:32:35+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-03T11:34:23+00:00","dateAnswered":"2023-08-02T20:11:28+00:00","acceptedAnswers":[{"commentID":388600,"body":"

I'm not sure I understand the goal. If you want to see the days between two column, you will need to already have a date in each of those columns. This seems to contradict stopping the count once one of the columns is populated. Can you provide more detail, or maybe a screenshot\/mockup of what you are looking for?<\/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":108410,"type":"question","name":"Using the \"Created Date\" field in formulas","excerpt":"Hello - I am creating a number of fields in various sheets to calculate lead times. One KPI we want to track is the life cycle of a project. I have added the \"Created Date\" field to all sheets and have an additional field that populates an Invoiced Date. I want a new field that will calculate the number of days between the…","snippet":"Hello - I am creating a number of fields in various sheets to calculate lead times. One KPI we want to track is the life cycle of a project. I have added the \"Created Date\" field…","categoryID":322,"dateInserted":"2023-08-02T13:45:01+00:00","dateUpdated":null,"dateLastComment":"2023-08-02T17:51:15+00:00","insertUserID":141394,"insertUser":{"userID":141394,"name":"KGetchell","url":"https:\/\/community.smartsheet.com\/profile\/KGetchell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-02T18:07:47+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-08-02T18:59:20+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":30,"score":null,"hot":3381985576,"url":"https:\/\/community.smartsheet.com\/discussion\/108410\/using-the-created-date-field-in-formulas","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108410\/using-the-created-date-field-in-formulas","format":"Rich","lastPost":{"discussionID":108410,"commentID":388557,"name":"Re: Using the \"Created Date\" field in formulas","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388557#Comment_388557","dateInserted":"2023-08-02T17:51:15+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-08-02T18:59:20+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\/C0IGNY6CEA25\/capture-jpg.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.JPG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-03T09:13:22+00:00","dateAnswered":"2023-08-02T15:09:48+00:00","acceptedAnswers":[{"commentID":388501,"body":"

The Created (date) field is not the issue in this instance. The problem is the data in your Invoiced Date column is actually text strings instead of date values.<\/p>


<\/p>

Try putting this into a date type column and using this new column in place of the Invoiced Date column in your original formula.<\/p>

=DATE(VALUE(MID([Invoiced Date (EP)]@row, FIND(\" \", [Invoiced Date (EP]@row) - 4, 4)), VALUE(LEFT([Invoiced Date (EP)]@row, FIND(\"\/\", [Invoiced Date (EP)]@row) - 1)), VALUE(MID([Invoiced Date (EP)]@row, FIND(\"\/\", [Invoiced Date (EP)]@row) + 1, FIND(\"\/\", [Invoiced Date (EP)]@row, FIND(\"\/\", [Invoiced Date (EP)]@row) + 1) - (FIND(\"\/\", [Invoiced Date (EP)]@row) + 1))))<\/p>"},{"commentID":388539,"body":"

Double check that you are putting it in a date type column. Can you also provide a screenshot of those same rows with the errors but also showing the Invoiced Date? It is working for me.<\/p>


<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/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":[]}],"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