How can I do an IF Statement with Years

Julie Kelsch
Julie Kelsch ✭
inFormulas and Functions

Hello,


I have been trying to find an answer to my problem through the forums but have been unable to solve it myself currently.

What I am trying to do is set up a formula that tells us when employees get vacation days. How our company currently works is as follows:

If employee has been here 1 year, they get 5 days vacation

If they have been here 2 years, 6 days vacation.

3 years = 7 days vacation.

4years = 8 days vacation, and so on until they max out at 10 days vacation days.


I also need to be able to deduct the amount of vacation days they have already used (reflected against another column where we already have the formula working). For the sake of ease will call this column "Vacation Days Used" and the formula I need help making would be under "Vacation days available"

So, How do I set up an If statement that reads: If hire date is greater than 1 year = 5, if hire date is greater than 2 years = 6, if hire date is greater than 3 years = 7 days, if hire date is greater than 4 years = 8 days, if hire date is greater than 5 years = 9 days, if hire date is greater than 6 years = 10 days + - "Vacation Days Used" Column

Thank you! I appreciate any help I can get in making this formula.

· Share on Facebook Share on Twitter

Best Answers

Answers

  • Julie Kelsch
    Julie Kelsch ✭

    @Nic LarsenYou are a mad genius !! That worked perfectly. Thank you thank you!!!

    · Share on Facebook Share on Twitter
  • Julie Kelsch
    Julie Kelsch ✭

    @John JonassenThank you! I'll take a look at that. I'm saving all of these formulas like a hoarder.

    · Share on Facebook Share on Twitter
  • John Jonassen
    John Jonassen ✭✭✭✭

    HA!! It's doing the same that@Nic Larsenprovided, within a single column. It's dividing the difference between today's date and the Hire Date by the 365 days in the year. That will then Round down to the nearest full completed year of service. Then based that, you get if it equals 1, 5 Days, 2, 6 Days, and so on. I think I got lost at the end and mixed up the 6 years and 10 days. But at least the idea is planted.

    · Share on Facebook Share on Twitter
  • Julie Kelsch
    Julie Kelsch ✭

    Haha I think so too! But I really appreciate all sorts of different examples. It helps my brain function the formulas out better in the future. Haha but yes I definitely get the idea. So excited!

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
For your first one with the DISTINCT function, you just need to move one of the closing parenthesis from the very end to to after [PRODUCTS OTHER]@row. You need to close the DISTINCT function before you enter your delimiter for the JOIN function.<\/p>


<\/p>

For the second piece, you would \"add\" the INDEX\/COLLECT to the JOIN.<\/p>

=INDEX(...........) + \" , \" + JOIN(..........)<\/p>"},{"commentID":397706,"body":"

My suggestion would be to swap the comma out for a line break and convert the column into a multi-select dropdown column.<\/p>

=INDEX(COLLECT({PROFIT CENTRE}, {PRODUCT MATCH}, [PRIMARY PRODUCT]@row), 1) + CHAR(10)<\/strong> + JOIN(DISTINCT(COLLECT({PROFIT CENTRE}, {PRODUCT MATCH}, CONTAINS(@cell, [PRODUCTS OTHER]@row))), CHAR(10)<\/strong>)<\/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":111020,"type":"question","name":"Count Active assignments by individual","excerpt":"=COUNTIFS([Assigned To]:[Assigned To], (\"Name\"))AND [Request Status];[Request Status], is one of \"In Progress\", \"Submitted\", \"Completed\") My countifs statement works by name but I want to further refine this by [Request Status];[Request Status], is one of \"In Progress\", \"Submitted\", \"Completed\")","snippet":"=COUNTIFS([Assigned To]:[Assigned To], (\"Name\"))AND [Request Status];[Request Status], is one of \"In Progress\", \"Submitted\", \"Completed\") My countifs statement works by name but I…","categoryID":322,"dateInserted":"2023-09-29T22:29:48+00:00","dateUpdated":"2023-09-30T02:04:11+00:00","dateLastComment":"2023-09-30T02:25:03+00:00","insertUserID":167772,"insertUser":{"userID":167772,"name":"Amy A","url":"https:\/\/community.smartsheet.com\/profile\/Amy%20A","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!g8fH33E0hT4!pJjG6aF-q6Y!szfojKurQUY","dateLastActive":"2023-09-30T02:21:57+00:00","banned":0,"punished":0,"private":true,"label":"✭"},"updateUserID":167772,"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-10-02T01:33:19+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":18,"score":null,"hot":3392068491,"url":"https:\/\/community.smartsheet.com\/discussion\/111020\/count-active-assignments-by-individual","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111020\/count-active-assignments-by-individual","format":"Rich","lastPost":{"discussionID":111020,"commentID":397774,"name":"Re: Count Active assignments by individual","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397774#Comment_397774","dateInserted":"2023-09-30T02:25:03+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-10-02T01:33:19+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-10-02T08:39:16+00:00","dateAnswered":"2023-09-30T02:23:29+00:00","acceptedAnswers":[{"commentID":397773,"body":"

Resolved - =COUNTIFS([Request Status]:[Request Status], OR(@cell = \"In Progress\", @cell = \"Submitted\", @cell = \"Closed\"), [Assigned To]:[Assigned To], \"Name\")<\/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":111001,"type":"question","name":"If then formula help","excerpt":"Hi All! I have a column , Average Score, that has been calculated using other fields. I want to use the average score to determine if an initiative is a program or project. If the initiative’s average score is between: 5 and 9 it is considered a project and if the score is between 10 and 15, it is considered a program. I…","snippet":"Hi All! I have a column , Average Score, that has been calculated using other fields. I want to use the average score to determine if an initiative is a program or project. If the…","categoryID":322,"dateInserted":"2023-09-29T16:44:08+00:00","dateUpdated":null,"dateLastComment":"2023-09-29T16:50:42+00:00","insertUserID":83926,"insertUser":{"userID":83926,"name":"Brittany Graves","url":"https:\/\/community.smartsheet.com\/profile\/Brittany%20Graves","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!u7_Cp_0Sp_w!g9FB3_V0hXw!s8EmBme9iOr","dateLastActive":"2023-09-29T17:36:04+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-10-02T13:30:25+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":22,"score":null,"hot":3392013890,"url":"https:\/\/community.smartsheet.com\/discussion\/111001\/if-then-formula-help","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111001\/if-then-formula-help","format":"Rich","lastPost":{"discussionID":111001,"commentID":397710,"name":"Re: If then formula help","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397710#Comment_397710","dateInserted":"2023-09-29T16:50:42+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-10-02T13:30:25+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-09-29T16:48:52+00:00","dateAnswered":"2023-09-29T16:47:45+00:00","acceptedAnswers":[{"commentID":397708,"body":"

You would use something like this:<\/p>

=IF(AND([Average Score]@row>= 5, [Average Score]@row<= 9), \"Project\", IF(AND([Average Score]@row>= 10, [Average Score]@row<= 15), \"Program\"))<\/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

  • IndexCollect / JoinCollect for Multi Select Columns

    I have one column called PRIMARY PRODUCT which is a single select dropdown and another called PRODUCTS OTHER which is a multi-select drop down. I have a third column called PROFIT CENTRE CODE which needs to populate based on all the products selected in the products columns. I have worked out a formula for PRODUCTS OTHER…

  • Count Active assignments by individual

    =COUNTIFS([Assigned To]:[Assigned To], ("Name"))AND [Request Status];[Request Status], is one of "In Progress", "Submitted", "Completed") My countifs statement works by name but I want to further refine this by [Request Status];[Request Status], is one of "In Progress", "Submitted", "Completed")

  • If then formula help

    Hi All! I have a column , Average Score, that has been calculated using other fields. I want to use the average score to determine if an initiative is a program or project. If the initiative’s average score is between: 5 and 9 it is considered a project and if the score is between 10 and 15, it is considered a program. I…