Multiple IFs around Dates

Michelle Choate 2
Michelle Choate 2 ✭✭✭✭✭
inFormulas and Functions

I am trying to create a column formula that says if [Date of Record]@row is less than a specific date (7/14/23) for instance, then return the number 43. If it is between 7/14/23 and 8/22/23 then 46, and so on. Is this possible?

Tags:
· Share on Facebook Share on Twitter

Best Answer

Answers

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公式手册模板!
@Ulrich Anderhub 1<\/a> That formula looks weird. What are you trying to do?<\/p>

I think you are trying to do =IF(AND(CONTAINS(Number@row, {Wave B (EMEA) - L4 Mapping}), CONTAINS(\"Be\", {Wave B (EMEA) - Country x2}), \"True\", \"False\")<\/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":111984,"type":"question","name":"Added TODAY() to a formula that works and get an error","excerpt":"Goal is to have a symbol represent how much newer the forecasted date is to TODAY. Any help would be much appreciated.","snippet":"Goal is to have a symbol represent how much newer the forecasted date is to TODAY. Any help would be much appreciated.","categoryID":322,"dateInserted":"2023-10-20T20:35:04+00:00","dateUpdated":null,"dateLastComment":"2023-10-20T22:41:11+00:00","insertUserID":162896,"insertUser":{"userID":162896,"name":"PR12x3","title":"Senior Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/PR12x3","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-20T23:36:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":163506,"lastUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-10-20T22:40:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":31,"score":null,"hot":3395677575,"url":"https:\/\/community.smartsheet.com\/discussion\/111984\/added-today-to-a-formula-that-works-and-get-an-error","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111984\/added-today-to-a-formula-that-works-and-get-an-error","format":"Rich","lastPost":{"discussionID":111984,"commentID":401050,"name":"Re: Added TODAY() to a formula that works and get an error","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401050#Comment_401050","dateInserted":"2023-10-20T22:41:11+00:00","insertUserID":163506,"insertUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-10-20T22:40:43+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\/I1RSSUK7PSEV\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/I1RSSUK7PSEV\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/I1RSSUK7PSEV\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/I1RSSUK7PSEV\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/I1RSSUK7PSEV\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/I1RSSUK7PSEV\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-20T22:38:59+00:00","dateAnswered":"2023-10-20T21:08:05+00:00","acceptedAnswers":[{"commentID":401042,"body":"

@PR12x3<\/a> <\/p>


<\/p>

Your Today call in your first syntax is incorrect. instead of AND(Today), it should be AND(TODAY()<\/p>

You also do not need @row after the Today() function. Today is pulling from your local date on your workstation<\/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":111965,"type":"question","name":"Using a cell instead of a range for IF statement?","excerpt":"Hi all - looking to average a range based on whether or not a neighboring cell has a value of greater than zero - all I get are errors. any help is appreciated, TIA","snippet":"Hi all - looking to average a range based on whether or not a neighboring cell has a value of greater than zero - all I get are errors. any help is appreciated, TIA","categoryID":322,"dateInserted":"2023-10-20T16:14:41+00:00","dateUpdated":null,"dateLastComment":"2023-10-20T17:54:47+00:00","insertUserID":132741,"insertUser":{"userID":132741,"name":"Jaime Holder","url":"https:\/\/community.smartsheet.com\/profile\/Jaime%20Holder","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZJM3D1OZEHQY\/nLRP1PJDG6PRE.jpg","dateLastActive":"2023-10-20T16:44:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":163506,"lastUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-10-20T22:40:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":31,"score":null,"hot":3395644768,"url":"https:\/\/community.smartsheet.com\/discussion\/111965\/using-a-cell-instead-of-a-range-for-if-statement","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111965\/using-a-cell-instead-of-a-range-for-if-statement","format":"Rich","lastPost":{"discussionID":111965,"commentID":401001,"name":"Re: Using a cell instead of a range for IF statement?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401001#Comment_401001","dateInserted":"2023-10-20T17:54:47+00:00","insertUserID":163506,"insertUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-10-20T22:40:43+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\/TM6EY8YG2JWJ\/screenshot-2023-10-20-121150.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/TM6EY8YG2JWJ\/screenshot-2023-10-20-121150.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/TM6EY8YG2JWJ\/screenshot-2023-10-20-121150.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/TM6EY8YG2JWJ\/screenshot-2023-10-20-121150.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/TM6EY8YG2JWJ\/screenshot-2023-10-20-121150.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/TM6EY8YG2JWJ\/screenshot-2023-10-20-121150.png"},"alt":"Screenshot 2023-10-20 121150.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-20T16:45:49+00:00","dateAnswered":"2023-10-20T16:42:30+00:00","acceptedAnswers":[{"commentID":400988,"body":"

@Jaime Holder<\/a> <\/p>

Try this:<\/p>

=IF([# of SO's]@row > 0, AVG([%age]$1:[%age]8), \" \")<\/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

  • IF AND Formula Question

    I wanted get the below formula running... but get an an #UNPARSEABLE error. =IF AND(Number@row, {Wave B (EMEA) - L4 Mapping}, CONTAINS({Wave B (EMEA) - Country x2}, "BE"), "True", "False") Any help would be much appreciated.

  • Added TODAY() to a formula that works and get an error

    Goal is to have a symbol represent how much newer the forecasted date is to TODAY. Any help would be much appreciated.

  • Using a cell instead of a range for IF statement?

    Hi all - looking to average a range based on whether or not a neighboring cell has a value of greater than zero - all I get are errors. any help is appreciated, TIA