Date comparison, if greater than 30 then flag
Hello,
I have two date columns that I would like to compare. The first column is named Requested and the second column is named [Sign off on]. In a third column formatted for symbols is where I've written the following formula,
=IF([SIGN OFF ON]@row + 45 > REQUESTED@row, 1)
I would like to get a flag if the [sign off on] column is 45 days greater than the requested column.
The problem is that it is flagging everything. Any suggestions?
Thanks,
Mily Oti
Best Answer
-
Mily Oti ✭✭Figured it out. The +45 needs to be with the Requested.
Answers
-
Mark Cronk ✭✭✭✭✭✭=IF([SIGN OFF ON]@row + 45 > REQUESTED@row, 1,0)
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Mily Oti ✭✭
Hi Mark,
Thanks for your reply. I added the 0 but it still flags every single row, even when there's only a day difference between the two.
If you can think of anything else, I would appreciate it.
Mily
-
Mily Oti ✭✭
Figured it out. The +45 needs to be with the Requested.
Help Article Resources
Categories
I hope this solves the problem. The problem is so granular, you'll laugh. Look at the quotation marks around Not Started and see how they are different than what's around Red and Green. <\/p>
The quotation marks around Not Started are curly and need to be straight like with Red and Green. Maybe you were tinkering with the formula in a text editor or word docs. Anyway, I hope this solves the problem. Good Luck and please let me know, if this solved it. <\/p>
=IF(AND([Status]@row = \"Not Started\", [Start]@row <= TODAY()), \"Red\", \"Green\")<\/p>
thanks<\/p>
Michael<\/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":111670,"type":"question","name":"Check Box Selected \/ Not Selected = YES or No","excerpt":"High Smartsheets Community, Question. Is there a formula for if a check box in a column is selected that it would return an answer of \"yes\" in another column and vice versa, if a check box is not selected it would return an answer of \"no.\" I know I can do a drop down, but there are some other factors that require me to…","snippet":"High Smartsheets Community, Question. Is there a formula for if a check box in a column is selected that it would return an answer of \"yes\" in another column and vice versa, if a…","categoryID":322,"dateInserted":"2023-10-13T21:29:00+00:00","dateUpdated":null,"dateLastComment":"2023-10-14T00:32:21+00:00","insertUserID":147643,"insertUser":{"userID":147643,"name":"Anthony DAmbrosio","url":"https:\/\/community.smartsheet.com\/profile\/Anthony%20DAmbrosio","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-14T00:27:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":147643,"lastUser":{"userID":147643,"name":"Anthony DAmbrosio","url":"https:\/\/community.smartsheet.com\/profile\/Anthony%20DAmbrosio","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-14T00:27:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":21,"score":null,"hot":3394477281,"url":"https:\/\/community.smartsheet.com\/discussion\/111670\/check-box-selected-not-selected-yes-or-no","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111670\/check-box-selected-not-selected-yes-or-no","format":"Rich","lastPost":{"discussionID":111670,"commentID":400020,"name":"Re: Check Box Selected \/ Not Selected = YES or No","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400020#Comment_400020","dateInserted":"2023-10-14T00:32:21+00:00","insertUserID":147643,"insertUser":{"userID":147643,"name":"Anthony DAmbrosio","url":"https:\/\/community.smartsheet.com\/profile\/Anthony%20DAmbrosio","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-14T00:27: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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-14T00:31:43+00:00","dateAnswered":"2023-10-13T21:43:32+00:00","acceptedAnswers":[{"commentID":400009,"body":"
Hi @Anthony DAmbrosio<\/a> <\/p> Checkboxes use a 1 for True (yes) and a 0 for False (no). <\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/JKSNRCHKMVDD\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
