Formula help for new user

AircraftLog
AircraftLog ✭
edited 12/09/19 inFormulas and Functions

Will anyone be willing to help me with formula in smartsheet. I am new user and have lots of problems I would like to sort out. But let me start with the most urgent.

I have column 1(SA), 2(SB), 3(SC), 4(ST) and would like to have column 5(CLIN) be filled based on column 1,2,3,4. When column 1 will show number (price) than 2,3 and 4 will show $0.00. There would never be number bigger than 0.00 in more than one column. I would like to achieve that when column 1 is greater than 0.00 then in column 5 there will be text CLIN - SA, When column 2 is greater then 0.00 then column 5 is CLIN - SB, column 3 greater then 0.00 - CLIN -SC, column 4 greater then 0.00 then column 5 CLIN - ST.

Rafal

Tags:
· Share on Facebook Share on Twitter

Comments

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
Try removing one of the closing parenthesis after the DATE function.<\/p>"},{"commentID":388644,"body":"

Thanks! That sorted it!😀<\/span><\/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":108438,"type":"question","name":"NETWORKDAYS WITH AN IF??","excerpt":"Can anyone help with a formula to track the number of days between 2 separate columns with the IF of stopping the count once a date is populated in one of those columns?","snippet":"Can anyone help with a formula to track the number of days between 2 separate columns with the IF of stopping the count once a date is populated in one of those columns?","categoryID":322,"dateInserted":"2023-08-02T19:37:00+00:00","dateUpdated":null,"dateLastComment":"2023-08-02T20:11:28+00:00","insertUserID":164426,"insertUser":{"userID":164426,"name":"JT_22002","url":"https:\/\/community.smartsheet.com\/profile\/JT_22002","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-03T11:32:53+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-03T12:00:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":17,"score":null,"hot":3382012708,"url":"https:\/\/community.smartsheet.com\/discussion\/108438\/networkdays-with-an-if","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108438\/networkdays-with-an-if","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108438,"commentID":388600,"name":"Re: NETWORKDAYS WITH AN IF??","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388600#Comment_388600","dateInserted":"2023-08-02T20:11:28+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-03T12:00:13+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-03T11:34:23+00:00","dateAnswered":"2023-08-02T20:11:28+00:00","acceptedAnswers":[{"commentID":388600,"body":"

I'm not sure I understand the goal. If you want to see the days between two column, you will need to already have a date in each of those columns. This seems to contradict stopping the count once one of the columns is populated. Can you provide more detail, or maybe a screenshot\/mockup of what you are looking for?<\/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":108410,"type":"question","name":"Using the \"Created Date\" field in formulas","excerpt":"Hello - I am creating a number of fields in various sheets to calculate lead times. One KPI we want to track is the life cycle of a project. I have added the \"Created Date\" field to all sheets and have an additional field that populates an Invoiced Date. I want a new field that will calculate the number of days between the…","snippet":"Hello - I am creating a number of fields in various sheets to calculate lead times. One KPI we want to track is the life cycle of a project. I have added the \"Created Date\" field…","categoryID":322,"dateInserted":"2023-08-02T13:45:01+00:00","dateUpdated":null,"dateLastComment":"2023-08-02T17:51:15+00:00","insertUserID":141394,"insertUser":{"userID":141394,"name":"KGetchell","url":"https:\/\/community.smartsheet.com\/profile\/KGetchell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-02T18:07:47+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-08-02T18:59:20+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":28,"score":null,"hot":3381985576,"url":"https:\/\/community.smartsheet.com\/discussion\/108410\/using-the-created-date-field-in-formulas","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108410\/using-the-created-date-field-in-formulas","format":"Rich","lastPost":{"discussionID":108410,"commentID":388557,"name":"Re: Using the \"Created Date\" field in formulas","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388557#Comment_388557","dateInserted":"2023-08-02T17:51:15+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-08-02T18:59:20+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\/C0IGNY6CEA25\/capture-jpg.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.JPG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-03T09:13:22+00:00","dateAnswered":"2023-08-02T15:09:48+00:00","acceptedAnswers":[{"commentID":388501,"body":"

The Created (date) field is not the issue in this instance. The problem is the data in your Invoiced Date column is actually text strings instead of date values.<\/p>


<\/p>

Try putting this into a date type column and using this new column in place of the Invoiced Date column in your original formula.<\/p>

=DATE(VALUE(MID([Invoiced Date (EP)]@row, FIND(\" \", [Invoiced Date (EP]@row) - 4, 4)), VALUE(LEFT([Invoiced Date (EP)]@row, FIND(\"\/\", [Invoiced Date (EP)]@row) - 1)), VALUE(MID([Invoiced Date (EP)]@row, FIND(\"\/\", [Invoiced Date (EP)]@row) + 1, FIND(\"\/\", [Invoiced Date (EP)]@row, FIND(\"\/\", [Invoiced Date (EP)]@row) + 1) - (FIND(\"\/\", [Invoiced Date (EP)]@row) + 1))))<\/p>"},{"commentID":388539,"body":"

Double check that you are putting it in a date type column. Can you also provide a screenshot of those same rows with the errors but also showing the Invoiced Date? It is working for me.<\/p>


<\/p>

\n
\n \n \"image.png\"<\/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":[]}],"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

  • Date formula to set cut off period

    I am trying to set a date formula to for the following statement; if the [Next Due Date] is <= 27/07/2023 return "Performed" Example: =IF(ISBLANK([Last inspection]@row), "", IF(DATE(2023,7,27)) < [Next Due Date]@row, "Overdue", "Performed"))

  • NETWORKDAYS WITH AN IF??

    Can anyone help with a formula to track the number of days between 2 separate columns with the IF of stopping the count once a date is populated in one of those columns?

  • Using the "Created Date" field in formulas

    Hello - I am creating a number of fields in various sheets to calculate lead times. One KPI we want to track is the life cycle of a project. I have added the "Created Date" field to all sheets and have an additional field that populates an Invoiced Date. I want a new field that will calculate the number of days between the…