Future Date Calculation

Mandi
Mandi
edited 12/09/19 inFormulas and Functions

Hi

I'm trying to convert an Excel formula to Smartsheet formula:

Excel formula =[Installation date]4+(365*[Warranty Expiry]$3)

Aim is to give a future date when the warranty will expire x years after the piece was installed.

Thanks

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册template!
Hey @Adriane Price<\/a> <\/p>

I'm having trouble following what columns are on what sheets in your data collection. If the different columns are all either on the source sheet or the destination sheet, you should be able to pull in the data using a COLLECT without having to concatenate. You cannot use a cross sheet reference range in Concatenate as you're trying to do.<\/p>

If your Lookup sheet now has the additional columns of theater and Line of business, the COLLECT should work for you. After creating cross sheet references to each of the individual columns in your lookup sheet, you should be able to filter against the appropriate criteria. Your ranges might be named differently but the formula will look something like. <\/p>

=INDEX(COLLECT({Lookup sheet Sub_Org column}, {Lookup sheet LS Manager}, [Last Name, First Name (VP)]@row, {Lookup sheet Theater column}, Theater@row, {Lookup sheet Line of Business column}, [Line of Business]@row),1)<\/p>

What does this get you?<\/p>

Kelly<\/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":108557,"type":"question","name":"If OR and SUM formula","excerpt":"Hi, I created this formula but I'm not sure why I'm getting the error message \"INVALID DATA TYPE\" =IF(OR(Name@row = \"AB\", \"CD\"), [Number1]@row, SUM([Number2]@row:[Number5]@row))","snippet":"Hi, I created this formula but I'm not sure why I'm getting the error message \"INVALID DATA TYPE\" =IF(OR(Name@row = \"AB\", \"CD\"), [Number1]@row, SUM([Number2]@row:[Number5]@row))","categoryID":322,"dateInserted":"2023-08-04T19:31:57+00:00","dateUpdated":null,"dateLastComment":"2023-08-04T20:16:16+00:00","insertUserID":140693,"insertUser":{"userID":140693,"name":"User251","title":"","url":"https:\/\/community.smartsheet.com\/profile\/User251","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-04T20:13:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":140693,"lastUser":{"userID":140693,"name":"User251","title":"","url":"https:\/\/community.smartsheet.com\/profile\/User251","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-04T20:13:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":40,"score":null,"hot":3382358893,"url":"https:\/\/community.smartsheet.com\/discussion\/108557\/if-or-and-sum-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108557\/if-or-and-sum-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108557,"commentID":389044,"name":"Re: If OR and SUM formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389044#Comment_389044","dateInserted":"2023-08-04T20:16:16+00:00","insertUserID":140693,"insertUser":{"userID":140693,"name":"User251","title":"","url":"https:\/\/community.smartsheet.com\/profile\/User251","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-04T20:13:30+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-04T20:16:32+00:00","dateAnswered":"2023-08-04T20:11:32+00:00","acceptedAnswers":[{"commentID":389040,"body":"

You are close, your syntax is just off by a bit.<\/p>

=IF(OR(Name@row = \"AB\", Name@row = \"CD\"), [Number1]@row, SUM([Number2]@row:[Number5]@row))<\/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":108545,"type":"question","name":"If, Index & Collect Formula issues","excerpt":"I have an issue with my current If, Index, and Collect formula. I have two sheets. One for data collection, and another for the Reviewer's Agency, Schedule, and Email address. I want to automate this process where if I have a TFD C shift, the Reviewers email address will be added into the data collection sheet. Here's my…","snippet":"I have an issue with my current If, Index, and Collect formula. I have two sheets. One for data collection, and another for the Reviewer's Agency, Schedule, and Email address. I…","categoryID":322,"dateInserted":"2023-08-04T16:47:48+00:00","dateUpdated":null,"dateLastComment":"2023-08-04T21:01:33+00:00","insertUserID":134622,"insertUser":{"userID":134622,"name":"Office of the Medical Director","url":"https:\/\/community.smartsheet.com\/profile\/Office%20of%20the%20Medical%20Director","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-04T21:09:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":134622,"lastUser":{"userID":134622,"name":"Office of the Medical Director","url":"https:\/\/community.smartsheet.com\/profile\/Office%20of%20the%20Medical%20Director","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-04T21:09:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":50,"score":null,"hot":3382353561,"url":"https:\/\/community.smartsheet.com\/discussion\/108545\/if-index-collect-formula-issues","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108545\/if-index-collect-formula-issues","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108545,"commentID":389049,"name":"Re: If, Index & Collect Formula issues","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389049#Comment_389049","dateInserted":"2023-08-04T21:01:33+00:00","insertUserID":134622,"insertUser":{"userID":134622,"name":"Office of the Medical Director","url":"https:\/\/community.smartsheet.com\/profile\/Office%20of%20the%20Medical%20Director","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-04T21:09:04+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\/K7YSB4ZJ9B09\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-04T21:01:11+00:00","dateAnswered":"2023-08-04T19:31:13+00:00","acceptedAnswers":[{"commentID":389035,"body":"

Try an INDEX\/COLLECT instead.<\/p>

=INDEX(COLLECT({Contact Column}, {Agency Column}, @cell = Agency@row, {Schedule Column}, @cell = Schedule@row), 1)<\/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"}]}],"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