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.

Add Years to a Date

Amanda Chu ✭✭

04/19/16 edited 12/09/19 inArchived 2016 Posts

If Term_Effective_Date = 12/07/15 and Terms_In_Year = 3 Years.

What is the formula to calcuate the New Date? I need to add 3 years to 12/07/15.

=DATE(YEAR(Term_Effecitve_Date2) + [Terms_In _Year]2, MONTH(Term_Effecitve_Date2), DAY(Term_Effecitve_Date2))

I am getting #NUMBER EXPECTED as an error.

Thank you for helping!

Tags:

0 ·

Comments

Brett Evans ✭✭✭✭✭✭

04/19/16

Amanda

try changing your column properties to date if it is set at Text/Number.

0 · Share on Facebook Share on Twitter
Amanda Chu ✭✭

04/19/16 edited 04/19/16

Thanks, that works!

0 · Share on Facebook Share on Twitter

This discussion has been closed.

Categories

@v.winters<\/a> <\/p>

If the two percent columns are formatted as percent columns (see screenshot), then the % Not Complete column would have this formula: <\/p>

=1 - [%Complete]<\/em>@row<\/p>

\n \n \n $\"Menu.PNG\"$ <\/img><\/a>\n <\/div>\n<\/div>\n
Hope this helps!<\/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":107018,"type":"question","name":"ROI Calculation Formula","excerpt":"I have a sheet that gathers numbers for various project costs and for various values. There are two columns that sum each of these; Total Costs and Total Value. I am using the following formula to calculate the ROI: =([Total Value]@row - [Total Costs]@row) \/ [Total Costs]@row * 100 However, the calculation seems to be off.…","snippet":"I have a sheet that gathers numbers for various project costs and for various values. There are two columns that sum each of these; Total Costs and Total Value. I am using the…","categoryID":322,"dateInserted":"2023-06-28T14:24:22+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T15:14:39+00:00","insertUserID":159640,"insertUser":{"userID":159640,"name":"Katherine Simpson","url":"https:\/\/community.smartsheet.com\/profile\/Katherine%20Simpson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/EY1XYADLUP90\/n8693BY7J2F39.jpg","dateLastActive":"2023-06-28T15:13:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":159640,"lastUser":{"userID":159640,"name":"Katherine Simpson","url":"https:\/\/community.smartsheet.com\/profile\/Katherine%20Simpson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/EY1XYADLUP90\/n8693BY7J2F39.jpg","dateLastActive":"2023-06-28T15:13:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":23,"score":null,"hot":3375928741,"url":"https:\/\/community.smartsheet.com\/discussion\/107018\/roi-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107018\/roi-calculation-formula","format":"Rich","lastPost":{"discussionID":107018,"commentID":382858,"name":"Re: ROI Calculation Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382858#Comment_382858","dateInserted":"2023-06-28T15:14:39+00:00","insertUserID":159640,"insertUser":{"userID":159640,"name":"Katherine Simpson","url":"https:\/\/community.smartsheet.com\/profile\/Katherine%20Simpson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/EY1XYADLUP90\/n8693BY7J2F39.jpg","dateLastActive":"2023-06-28T15:13:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-28T15:14:06+00:00","dateAnswered":"2023-06-28T14:56:38+00:00","acceptedAnswers":[{"commentID":382852,"body":"
Try omitting the \"*100\" at the end of your formula. Convert the column to show the number as a percentage. <\/p>
\n
\n \n $\"Screenshot$ <\/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":[]},{"discussionID":107015,"type":"question","name":"Coiuntifs Formula Help Needed","excerpt":"Hi! Can anyone please assist with the correct Formula? i'm trying to count how many rows are on a sheet(Reference Sheet) the criterea for the rows are as follows: =countifs STATUS Column is \"Discharged\" or \"ACTIVE\" , Discharged\/Nonadmit Column is Greater than 05\/19\/2023 and Date activated column is less than 05\/27\/2023 I…","snippet":"Hi! Can anyone please assist with the correct Formula? i'm trying to count how many rows are on a sheet(Reference Sheet) the criterea for the rows are as follows: =countifs STATUS…","categoryID":322,"dateInserted":"2023-06-28T13:59:55+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T14:35:02+00:00","insertUserID":162885,"insertUser":{"userID":162885,"name":"Ymandel","title":"President","url":"https:\/\/community.smartsheet.com\/profile\/Ymandel","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-28T15:49:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162885,"lastUser":{"userID":162885,"name":"Ymandel","title":"President","url":"https:\/\/community.smartsheet.com\/profile\/Ymandel","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-28T15:49:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":29,"score":null,"hot":3375925497,"url":"https:\/\/community.smartsheet.com\/discussion\/107015\/coiuntifs-formula-help-needed","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107015\/coiuntifs-formula-help-needed","format":"Rich","lastPost":{"discussionID":107015,"commentID":382847,"name":"Re: Coiuntifs Formula Help Needed","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382847#Comment_382847","dateInserted":"2023-06-28T14:35:02+00:00","insertUserID":162885,"insertUser":{"userID":162885,"name":"Ymandel","title":"President","url":"https:\/\/community.smartsheet.com\/profile\/Ymandel","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-28T15:49:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-28T14:22:57+00:00","dateAnswered":"2023-06-28T14:10:08+00:00","acceptedAnswers":[{"commentID":382838,"body":"
@Ymandel<\/a> See if this version works:<\/p>
=COUNTIFS([Discharged Date]:[Discharged Date], >DATE(2023, 5, 26), [Date Activated]:[Date Activated], <=(DATE(2023, 6, 2)), [Status]:[Status], OR(@cell = \"Active\", @cell = \"Discharged\"))<\/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&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&siteSectionID=0&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 Posts","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">