Flagging a Date 30 days past due?
I have a sheet with the following columns:
- Last Updated: date in which a customer profile was last edited
- Next Review Due: a date calculated as 4 months after the Last Updated Date -- this is the time at which an account team should review the customer profile data to ensure that it is still up-to-date and if any changes are made. A notification is sent out to these folk on this date reminding them of the needed follow up.
Now, I would like to flag any rows which have had a month elapse since that notification was sent out, and no updates recorded.
I'm thinking perhaps via conditional formatting change the Next Review Due date RED if today is 30days or more past, but I'm open to other options.
What formula do I need?
Best Answers
-
Paul Newcome ✭✭✭✭✭✭If you insert a flag type column, you could use a formula such as...
=IF([Next Review Due]@row < TODAY(-30), 1)
If the Next Review Due date is more than 30 days in the past, it will flag the column. Of course you can change the 1 to whatever you want for the output.
From there you can set up your conditional formatting or additional alerts based on this new column.
-
Guaca Mohle ✭✭✭✭
That did the trick. Now, for a blank cell in Next Review Date column, I get a #INVALID OPERATION error. I've tried to use the IFERROR to just leave the cell blank, but not getting the syntax correct.
=IFERROR((IF([Next Review Due]@row < TODAY(-30), "No"),"")
-
Paul Newcome ✭✭✭✭✭✭
You just have an extra opening parenthesis after the IFERROR.
=IFERROR(IF([Next Review Due]@row < TODAY(-30), "No"), "")
Answers
-
Paul Newcome ✭✭✭✭✭✭
If you insert a flag type column, you could use a formula such as...
=IF([Next Review Due]@row < TODAY(-30), 1)
If the Next Review Due date is more than 30 days in the past, it will flag the column. Of course you can change the 1 to whatever you want for the output.
From there you can set up your conditional formatting or additional alerts based on this new column.
-
Guaca Mohle ✭✭✭✭
That did the trick. Now, for a blank cell in Next Review Date column, I get a #INVALID OPERATION error. I've tried to use the IFERROR to just leave the cell blank, but not getting the syntax correct.
=IFERROR((IF([Next Review Due]@row < TODAY(-30), "No"),"")
-
Paul Newcome ✭✭✭✭✭✭
You just have an extra opening parenthesis after the IFERROR.
=IFERROR(IF([Next Review Due]@row < TODAY(-30), "No"), "")
-
Guaca Mohle ✭✭✭✭
AWESOME!!!!!!!!!!
Help Article Resources
Categories
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//www.santa-greenland.com/community/discussion/74780/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/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":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":47,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+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-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"
