Help with date formula's
I am creating a tracker that will keep track of vacation days, and the number of days an employee gets each year depends on how long they have been an employee. The days are earned each year on their anniversary.
I have almost everything working with the exception of 1 thing.
Let's say I have an employee that started 10/12/2016, as of that date he will get 13 vacation days but not before that date.
Right now I have this as a formula in the cell for Vacation Days Alotted:
=IF(YEAR(TODAY()) - YEAR([Date of Hire]6) < 1, 3, IF(YEAR(TODAY()) - YEAR([Date of Hire]6) < 2, 5, IF(YEAR(TODAY()) - YEAR([Date of Hire]6) < 4, 10, IF(YEAR(TODAY()) - YEAR([Date of Hire]6) < 6, 13, IF(YEAR(TODAY()) - YEAR([Date of Hire]6) < 9, 15, 18)))))
My result is 13 days. Right now since it is not yet 10/12/20 it should only show 10 days. Does anyone have suggestions on what I need to change in my formula to correct this?
Thanks in advance for any help!
Regards,
Dana
Best Answer
-
Tori Lynn Mayfield EmployeeHi@Dana Wanner,
Great question! In reviewing your formula setup, it looks like any row with a start date listed in 2016 will produce 13 vacation days with your current setup. This is because only the year is taken into account in your function which produces the outcome of 2020 - 2016.
In order to take into account the start day, you may want to instead subtract the whole date from today's date in order to get the number of days between. This can then be divided by 365.25 (the additional .25 is to account for a leap year every 4 years). For example:
- =今天()-[雇佣日期]@row) / 365.25
这将produce a decimal, however, that you'll want to use the ROUNDDOWN() function on so that additional days aren't allocated prior to the employee's anniversary, like this:
- =ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25)
This would then replace your existing YEAR(TODAY()) - YEAR([Date of Hire]@row) section in the function resulting in:
- =IF(ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25) < 1, 3, IF(ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25) < 2, 5, IF(ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25) < 4, 10, IF(ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25) < 6, 13, IF(ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25) < 9, 15, 18)))))
In my test sheet, using your dates (with an extra row for today's date to ensure it's working as expected), this looks something like:
Note that I've added in the @row function to ensure this function can be added to any row on your sheet and will run more efficiently on the back end.
Check out these resources from our Learning Center for more on Date functions and using the @row reference in your future formulas:
- https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell
- https://help.smartsheet.com/articles/2477601-use-formulas-perform-calculations-dates
Hope this helps!
Thanks,
ToriLynn
Answers
-
Tori Lynn Mayfield Employee
Hi@Dana Wanner,
Great question! In reviewing your formula setup, it looks like any row with a start date listed in 2016 will produce 13 vacation days with your current setup. This is because only the year is taken into account in your function which produces the outcome of 2020 - 2016.
In order to take into account the start day, you may want to instead subtract the whole date from today's date in order to get the number of days between. This can then be divided by 365.25 (the additional .25 is to account for a leap year every 4 years). For example:
- =今天()-[雇佣日期]@row) / 365.25
这将produce a decimal, however, that you'll want to use the ROUNDDOWN() function on so that additional days aren't allocated prior to the employee's anniversary, like this:
- =ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25)
This would then replace your existing YEAR(TODAY()) - YEAR([Date of Hire]@row) section in the function resulting in:
- =IF(ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25) < 1, 3, IF(ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25) < 2, 5, IF(ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25) < 4, 10, IF(ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25) < 6, 13, IF(ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25) < 9, 15, 18)))))
In my test sheet, using your dates (with an extra row for today's date to ensure it's working as expected), this looks something like:
Note that I've added in the @row function to ensure this function can be added to any row on your sheet and will run more efficiently on the back end.
Check out these resources from our Learning Center for more on Date functions and using the @row reference in your future formulas:
- https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell
- https://help.smartsheet.com/articles/2477601-use-formulas-perform-calculations-dates
Hope this helps!
Thanks,
ToriLynn
-
Hello @ToriLynn,
Thank you so much, this is exactly what I was looking for. It works much better all the way around. I really
Help Article Resources
Categories
Check out theFormula Handbook template!
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":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+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-27T01:32: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":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+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-27T01:32: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-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=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":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-26T17:06:33+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-26T17:06:33+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":"
