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.
today() function
I would like to conditionally format date columns based on whether something is past due of not.
Can I automate that in smartsheets?
Comments
-
You can. If you create a conditional format on a date column there is an option to set formatting if the date is in the past or in the future. Hope this helps!
-
Travis EmployeeHeidi is correct! Check out this help center article on conditional formatting:http://help.smartsheet.com/customer/portal/articles/516359-conditional-formatting
-
ACharvet ✭
Awesome. Thanks guys.
Categories
The issue is with your arguments overall. Using the Score of 40 (as in your examples)...<\/p>
<\/p>
The score start range has to be 40 or greater, but the range end has to be less than or equal to 40.<\/p>
<\/p>
Try reversing it so that the start is less than the score and the end is greater than the score.<\/p>
<\/p>
Take a look at the second piece in your destination file. The formula is not throwing an error, but it is also not pulling the correct value in. It should be pulling in Moderate because 40 is between 35 and 54, but it is pulling in Moderate-High because that is the first entry where the Range Start is greater than 40.<\/p>"},{"commentID":387563,"body":"
You have your greater than and less than backwards. Your formula is saying to looking for the start date to be greater than 40 and the 35 is less than 40.<\/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":108180,"type":"question","name":"How can I make an approver's view on the message notification field mandatory?","excerpt":"Making it mandatory will not allow approver to approve or decline without acknowledgement. Example creating a column that would say: \"You acknowledged that accounting details have been reviewed?\" Then enters initials. This has to be done prior to clicking approved or decline.","snippet":"Making it mandatory will not allow approver to approve or decline without acknowledgement. Example creating a column that would say: \"You acknowledged that accounting details have…","categoryID":321,"dateInserted":"2023-07-27T14:08:57+00:00","dateUpdated":null,"dateLastComment":"2023-07-27T18:44:32+00:00","insertUserID":157632,"insertUser":{"userID":157632,"name":"Shirley Romero","url":"https:\/\/community.smartsheet.com\/profile\/Shirley%20Romero","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/6RM6Q0PW4X07\/nLP5DK4J5PI9I.jpg","dateLastActive":"2023-07-27T15:36:15+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-07-27T18:44:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":17,"score":null,"hot":3380952209,"url":"https:\/\/community.smartsheet.com\/discussion\/108180\/how-can-i-make-an-approvers-view-on-the-message-notification-field-mandatory","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108180\/how-can-i-make-an-approvers-view-on-the-message-notification-field-mandatory","format":"Rich","tagIDs":[300],"lastPost":{"discussionID":108180,"commentID":387613,"name":"Re: How can I make an approver's view on the message notification field mandatory?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387613#Comment_387613","dateInserted":"2023-07-27T18:44:32+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-07-27T18:44:42+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":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-27T15:38:54+00:00","dateAnswered":"2023-07-27T14:43:11+00:00","acceptedAnswers":[{"commentID":387545,"body":"
This cannot be set up through an Approval Automation. At his point you have a few options (each of which has its pros and cons).<\/p>
<\/p>
You can just include a blurb that says that they are acknowledging by submitting within the Approval automation.<\/p>
<\/p>
You can set up an update request automation as a reminder that they need to enter their initials if they submit but didn't the first time.<\/p>
<\/p>
The last option I can think of that would truly make it required is a bit complex but possible. The first step is to create an \"approval sheet\". This sheet will have the same fields in it that you need to send in the approval request and a form created to match.<\/p>
<\/p>
You would then take the base form URL and drop it into a formula on the original sheet that allows you to basically generate a custom form on each row that will have the row data prefilled when someone clicks on the URL in that row. Since you can make form fields required, this will give you the ability to require their acknowledgement prior to them submitting their approval.<\/p>
<\/p>
Send this custom form URL through an alert and instruct the users to click the link to open the approval form.<\/p>
<\/p>
The form entry would populate the \"approval sheet\", but you can use formulas with cross sheet references to pull the appropriate data back over to your original sheet.<\/p>
<\/p>
If that last option sounds like something you would be interested in, fell free to let me know, and I will try to pull together a few links to help you get started building the formula.<\/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":[{"tagID":300,"urlcode":"Email Notifications","name":"Email Notifications"}]},{"discussionID":108172,"type":"question","name":"Auto Fill a row from another row","excerpt":"I am trying to autofill a row with a specific number when a certain drop down in used in another column but I am getting an error. I am not seeing an Automation or a conditional format that supports this so I am assuming that this will require a function but I am not able to figure out which one. IE- When FY23 is chosen…","snippet":"I am trying to autofill a row with a specific number when a certain drop down in used in another column but I am getting an error. I am not seeing an Automation or a conditional…","categoryID":322,"dateInserted":"2023-07-27T11:24:15+00:00","dateUpdated":null,"dateLastComment":"2023-07-27T13:32:05+00:00","insertUserID":153244,"insertUser":{"userID":153244,"name":"Minni","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Minni","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/73SE548DKUM7\/nU7DY4I5SMNR3.jpg","dateLastActive":"2023-07-27T13:51:20+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":163439,"lastUser":{"userID":163439,"name":"Ramzi","title":"Smartsheet Solution Architect","url":"https:\/\/community.smartsheet.com\/profile\/Ramzi","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!HwjQ38yvgVY!xejdcS75MaY!5PoO6DBMoFm","dateLastActive":"2023-07-27T15:22:19+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":47,"score":null,"hot":3380923580,"url":"https:\/\/community.smartsheet.com\/discussion\/108172\/auto-fill-a-row-from-another-row","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108172\/auto-fill-a-row-from-another-row","format":"Rich","lastPost":{"discussionID":108172,"commentID":387510,"name":"Re: Auto Fill a row from another row","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387510#Comment_387510","dateInserted":"2023-07-27T13:32:05+00:00","insertUserID":163439,"insertUser":{"userID":163439,"name":"Ramzi","title":"Smartsheet Solution Architect","url":"https:\/\/community.smartsheet.com\/profile\/Ramzi","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!HwjQ38yvgVY!xejdcS75MaY!5PoO6DBMoFm","dateLastActive":"2023-07-27T15:22: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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/I1WA1YGXKVSR\/capture-jpg.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.JPG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-27T12:00:22+00:00","dateAnswered":"2023-07-27T11:46:01+00:00","acceptedAnswers":[{"commentID":387480,"body":"
Try this - <\/p>
In your Account Number (CPR) field, use this formula:<\/p>
=IF([Project Name]@row = \"FY23\", \"123-456-789\", IF([Project Name]@row = \"FY24\", \"987-654-321\",\"\"))<\/p>
If you have a lot of values, you may want to consider a reference sheet and then INDEX\/MATCH into that sheet.<\/p>
I hope this helps you. Have a great day!<\/p>
Ramzi<\/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":[]}">
