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.

=IF formula ???

grgonzalez11119061
grgonzalez11119061 ✭
edited 12/09/19 inArchived 2017 Posts

I am trying to create an alert formula. I need to contact people every 15 days. I have one cell that states "Last contact" and the date, another that states "Next contact"and the date, the third cell, I would like it to state "Contact" in red. Can anyone help me formulate the "Contact" cell?

Tags:
· Share on Facebook Share on Twitter

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    What are the conditions for the third cell? When do you want it to appear. Do you want it change color based on the date? Please give us more detail to help us understand what you are trying to accomplish in the final cell.

    · Share on Facebook Share on Twitter
  • grgonzalez11119061
    grgonzalez11119061 ✭

    Hello Mike,

    Thank you for your assistance. I've answered your questions below, I hope it is clear. I've also attached a picture of how I want it to look.

    1. What are the conditions for the third cell?Contact every 15 days from "Last Contacted" date

    2.When do you want it to appear?"Contact" should appear after the 15 days are up

    3. Do you want it change color based on the date?Yes, after the 15 days are up the Action box should read "Contact" (the entire box should be red with the word "Contact" in it)

    Capture.PNG

    · Share on Facebook Share on Twitter
  • grgonzalez11119061
    grgonzalez11119061 ✭

    thank you, I got it to work

    · Share on Facebook Share on Twitter
  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    =IF(TODAY() - [Date of Next Contact]1 >= 15, "Contact", "Contacted")

    You could change "contacted" to any other message or delete the word and leave the quotation marks to just have the cell blank.

    · Share on Facebook Share on Twitter
This discussion has been closed.

Categories

I would suggest a helper column that uses a nested IF to output which alert should be sent (or blank if no alert is to be sent). Then you can set your automation up to run weekly and use a condition of this helper column being in that particular range.<\/p>

=IF([Expiration Date]<= TODAY(30), \"30 Days\", IF([Expiration Date]@row<= TODAY(60), \"60 Days\", IF([Expiration Date]@row<= TODAY(90), \"90 Days\", IF([Expiration Date]@row<= TODAY(180), \"180 Days\"))))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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-09-29T16:56:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":13,"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-09-29T16:56: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-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":[]},{"discussionID":110985,"type":"question","name":"Dashboard Last Updated Date","excerpt":"I would like to display the last time the dashboard was updated. My approach was adding a date field in the summary data that automatically pulls the last saved date of the sheet. I don't think I can do a formula in the date field type. Any other suggestions?","snippet":"I would like to display the last time the dashboard was updated. My approach was adding a date field in the summary data that automatically pulls the last saved date of the sheet.…","categoryID":322,"dateInserted":"2023-09-29T12:56:38+00:00","dateUpdated":"2023-09-29T23:58:27+00:00","dateLastComment":"2023-09-29T16:19:19+00:00","insertUserID":167704,"insertUser":{"userID":167704,"name":"Jenn Moffett","title":"Director, PMO","url":"https:\/\/community.smartsheet.com\/profile\/Jenn%20Moffett","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-29T15:38:19+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":166028,"lastUserID":167704,"lastUser":{"userID":167704,"name":"Jenn Moffett","title":"Director, PMO","url":"https:\/\/community.smartsheet.com\/profile\/Jenn%20Moffett","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-29T15:38:19+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":30,"score":null,"hot":3391997757,"url":"https:\/\/community.smartsheet.com\/discussion\/110985\/dashboard-last-updated-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110985\/dashboard-last-updated-date","format":"Rich","tagIDs":[292,335],"lastPost":{"discussionID":110985,"commentID":397701,"name":"Re: Dashboard Last Updated Date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397701#Comment_397701","dateInserted":"2023-09-29T16:19:19+00:00","insertUserID":167704,"insertUser":{"userID":167704,"name":"Jenn Moffett","title":"Director, PMO","url":"https:\/\/community.smartsheet.com\/profile\/Jenn%20Moffett","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-29T15:38: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-09-29T15:39:37+00:00","dateAnswered":"2023-09-29T14:10:25+00:00","acceptedAnswers":[{"commentID":397662,"body":"

Hi @Jenn Moffett<\/a>,<\/p>

One approach is as follows.<\/p>

  1. Create a helper column. Name it \"Modified\", and select \"Modified Date\" as the column type. You can optionally hide this column.<\/li>
  2. Create a Sheet Summary field. Name is \"Last Updated\", and select \"Date\" as the column type.<\/li>
  3. Type the following formula into the \"Last Updated\" Sheet Summary field: =MAX(Modified:Modified)<\/li><\/ol>

    Hope that helps! I just tested it before recommending, and it worked like a charm! :)<\/p>

    BRgds,<\/p>

    -Ray<\/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":292,"urlcode":"dashboards","name":"Dashboards"},{"tagID":335,"urlcode":"sheet-summary","name":"Sheet Summary"}]}],"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":[]}">

    Trending Posts

    • Setting up a weekly automation to alert someone in 30, 60 and 90 days of a date

      I have a sheet that tracks employees certifications. I'd like to setup a single automation that runs weekly to alert the employee when the expiration date is within 180, 90, 60 and 30 days away. I'd prefer that the 180 day alert only be sent monthly until the 90 day date is hit, then weekly. I'd like the message sent to…

    • 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…

    • Dashboard Last Updated Date

      I would like to display the last time the dashboard was updated. My approach was adding a date field in the summary data that automatically pulls the last saved date of the sheet. I don't think I can do a formula in the date field type. Any other suggestions?