Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, pleaseVisit the Current Forums.
Formula for calculating years accurately
We need a formula for calculating years accurately when we compare a date with the date today – some examples would be:
- Age in years: Today() - date of birth, currently we use: =(TODAY() – DOB1) / 365.25 but it doesn’t always give the correct answer. The 365.25 is days in a year allowing for leap years, or
- Length of service in years: Today() – date joined, currently we use: =(TODAY() - [Started Work]1) / 365.25
Can anyone suggest a formula that works in all cases including where there are leap years in the intervening period?
Comments
-
SmSulli ✭Hi
Here's a formula I created to calculate years for you. Let me know if this works for you.
Age:
=IF(AND(MONTH(DOB1) >= MONTH(TODAY()), DAY(DOB1) > DAY(TODAY())), YEAR(TODAY()) - 1 - YEAR(DOB1), YEAR(TODAY()) - YEAR(DOB1))
Years of Service:
=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))
Enjoy!
-
StephenLake ✭✭
Thank you so much for your help - it works well.
Are you available to consult to us?
The next two formulas not working I need help with are:
Next Birthday:
=DOB3 + ([Age next Birthday]3 * 365.25) + 1
&
Next [Service] Anniversary:
=[Started Work]3 + ([Service next Anniversary]3 * 365.25)
There is probably a smarter way to do these.
Attached a screenshot. Please note dates are in DD/MM/YYYY format
-
SmSulli ✭
Thank you so much for the compliment. I'm a bit of an Excel nut too. But, I did come across an error myself. So I've made new formulas for Age and Years of Service. Also included formulas for Next Birthday, and Next Anniversary. I made the Next birthday and next anniversary formatted to DD/MM/YYYY.
Age:
=IF(MONTH(DOB1) > MONTH(TODAY()), YEAR(TODAY()) - 1 - YEAR(DOB1), IF(AND(MONTH(DOB1) >= MONTH(TODAY()), DAY(DOB1) > DAY(TODAY())), YEAR(TODAY()) - 1 - YEAR(DOB1), YEAR(TODAY()) - YEAR(DOB1)))
Years of Service:
=IF(MONTH([Start Date]1) > MONTH(TODAY()), YEAR(TODAY()) - 1 - YEAR([Start Date]1), 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)))
Next Birthday:
=IF(MONTH(DOB1) > MONTH(TODAY()), DAY(DOB1) + "/" + MONTH(DOB1) + "/" + YEAR(TODAY()), IF(AND(MONTH(DOB1) >= MONTH(TODAY()), DAY(DOB1) > DAY(TODAY())), DAY(DOB1) + "/" + MONTH(DOB1) + "/" + YEAR(TODAY()), DAY(DOB1) + "/" + MONTH(DOB1) + "/" + (YEAR(TODAY()) + 1)))
Next Anniversary:
=IF(MONTH([Start Date]1) > MONTH(TODAY()), DAY([Start Date]1) + "/" + MONTH([Start Date]1) + "/" + YEAR(TODAY()), IF(AND(MONTH([Start Date]1) >= MONTH(TODAY()), DAY([Start Date]1) > DAY(TODAY())), DAY([Start Date]1) + "/" + MONTH([Start Date]1) + "/" + YEAR(TODAY()), DAY([Start Date]1) + "/" + MONTH([Start Date]1) + "/" + (YEAR(TODAY()) + 1)))
Enjoy!
-
J. Craig Williams ✭✭✭✭✭✭My take on the formulas, partially because the "Next" formulas assume DD/MM/YY and I'm (for better or worse) using MM/DD/YY.
Age:
=IF(DATE(YEAR(TODAY()), MONTH(DOB1), DAY(DOB1)) < TODAY(), YEAR(TODAY()) - YEAR(DOB1), YEAR(TODAY()) - YEAR(DOB1) - 1)
Next Birthday:
=IF(DATE(YEAR(TODAY()), MONTH(DOB1), DAY(DOB1)) < TODAY(), DATE(YEAR(TODAY()) + 1, MONTH(DOB1), DAY(DOB1)), DATE(YEAR(TODAY()), MONTH(DOB1), DAY(DOB1)))
Years of Service:
=IF(DATE(YEAR(TODAY()), MONTH([Start Date]1), DAY([Start Date]1)) < TODAY(), YEAR(TODAY()) - YEAR([Start Date]1), YEAR(TODAY()) - YEAR([Start Date]1) - 1)
Next Anniversary:
=IF(DATE(YEAR(TODAY()), MONTH([Start Date]1), DAY([Start Date]1)) < TODAY(), DATE(YEAR(TODAY()) + 1, MONTH([Start Date]1), DAY([Start Date]1)), DATE(YEAR(TODAY()), MONTH([Start Date]1), DAY([Start Date]1)))
The two "Next" columns are assumed to be Date type columns.
Also, if you want to get rid of those pesky #INVALID DATA TYPE messages:
=IFERROR(formula here, "Need a date")
like this
=IFERROR(IF(DATE(YEAR(TODAY()), MONTH(DOB1), DAY(DOB1)) < TODAY(), DATE(YEAR(TODAY()) + 1, MONTH(DOB1), DAY(DOB1)), DATE(YEAR(TODAY()), MONTH(DOB1), DAY(DOB1))), "Need a date")
Craig
-
Meritza Webb ✭✭
I have tried all of these suggested formulas in an attempt to auto calculate age. None of the formulas are working for me. The results return as #DATE EXPECTED. Any new suggestions?
-
Meritza Webb ✭✭
Disregard. I figured out the issue. I had the Age field formatted as a date instead of text. It works now! Thanks for your help.
-
Meritza Webb ✭✭
I used this formula, but some of my ages are off by 1 year. Do you know why this may be happening?
=IF(AND(MONTH(DOB1) >= MONTH(TODAY()), DAY(DOB1) > DAY(TODAY())), YEAR(TODAY()) - 1 - YEAR(DOB1), YEAR(TODAY()) - YEAR(DOB1))
Categories
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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":109490,"type":"question","name":"HAS exact match within multiselect - Numbered Values","excerpt":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains))) formula, but having some false positives show up wherever a partial match is found. I later found some suggestions that (has) would be more…","snippet":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains)))…","categoryID":322,"dateInserted":"2023-08-25T19:26:32+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":154049,"lastUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":16,"score":null,"hot":3386003780,"url":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","format":"Rich","lastPost":{"discussionID":109490,"commentID":392694,"name":"Re: HAS exact match within multiselect - Numbered Values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392694#Comment_392694","dateInserted":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+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\/KJPRLKL2FW16\/capture-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:49:35+00:00","dateAnswered":"2023-08-25T23:58:23+00:00","acceptedAnswers":[{"commentID":392688,"body":"
Hi, <\/p>
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/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":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T14:46:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T14:46:22+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-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
