IF/AND Formula
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 argument"! Here is what I have:
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30), "Red")))
How do I fix??
Best Answers
-
Carson Penticuff ✭✭✭✭✭✭You were, indeed, very close.
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), "Red"))
-
SteyJ ✭✭✭
It looks like you forgot to close out theANDfuntion. Try this
=IF([Invoice Received?]@row = 0, "Green", IF(AND([Renewal Date]@row >= TODAY(0), [Renewal Date]@row <= TODAY(+30)), "Red", "Yellow"))Hope this helps!
Jacob Stey
Patriot Group International
Answers
-
Carson Penticuff ✭✭✭✭✭✭
You were, indeed, very close.
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), "Red"))
-
SteyJ ✭✭✭
It looks like you forgot to close out theANDfuntion. Try this
=IF([Invoice Received?]@row = 0, "Green", IF(AND([Renewal Date]@row >= TODAY(0), [Renewal Date]@row <= TODAY(+30)), "Red", "Yellow"))Hope this helps!
Jacob Stey
Patriot Group International
-
Pamela Wagner ✭✭✭✭✭✭
Thanks@SteyJ! I made one tweak:
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(0), [Renewal Date]@row <= TODAY(+30)), "Red", "Yellow"))
I don't need the Green dot that you put at the beginning but I do see what you did. I didn't put in the alternate, if this meets the condition do X/if it doesn't meet the condition do Y. I missed the 'doesn't meet condition' part.
-
Pamela Wagner ✭✭✭✭✭✭
Thanks also@Carson Penticuff! Your formula works as well and now I need to determine the difference! Always good to have options!
Help Article Resources
Categories
=COUNTIFS({Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1<\/strong>, IFERROR(YEAR(@cell), 0) = 2023<\/strong>))<\/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":108911,"type":"question","name":"IF formula not working for a drop down list","excerpt":"Hello, I'm trying to equate a numerical value to a drop down list when I receive a form entry. Here is my list: 4 - very clear 3 - clear 2 - somewhat clear 1 - not clear The formula I entered was =IF([Training Objectives]@row = \"1 - not clear\", 1, IF([Training Objectives]@row = \"2 - somewhat clear\", 2, IF([Training…","snippet":"Hello, I'm trying to equate a numerical value to a drop down list when I receive a form entry. Here is my list: 4 - very clear 3 - clear 2 - somewhat clear 1 - not clear The…","categoryID":322,"dateInserted":"2023-08-14T14:32:56+00:00","dateUpdated":null,"dateLastComment":"2023-08-14T15:02:24+00:00","insertUserID":143464,"insertUser":{"userID":143464,"name":"Courtney M","url":"https:\/\/community.smartsheet.com\/profile\/Courtney%20M","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZMErGR_kbWs!zDErcze5yPs!34pFnjKNrYv","dateLastActive":"2023-08-14T14:58:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-14T15:32:48+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":27,"score":null,"hot":3384050720,"url":"https:\/\/community.smartsheet.com\/discussion\/108911\/if-formula-not-working-for-a-drop-down-list","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108911\/if-formula-not-working-for-a-drop-down-list","format":"Rich","lastPost":{"discussionID":108911,"commentID":390469,"name":"Re: IF formula not working for a drop down list","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390469#Comment_390469","dateInserted":"2023-08-14T15:02:24+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-14T15:32:48+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-14T15:25:45+00:00","dateAnswered":"2023-08-14T14:38:51+00:00","acceptedAnswers":[{"commentID":390454,"body":"
