Creating an IF formula based off two columns where it can group the data by a timeline logic

Trying to find a way to automate our sheet by using the IF formula to pull data from two columns [status (i.e Sent, Scheduled) and date column] to build a logic to pull a timeline (i.e today, tomorrow, next week, 2 weeks, done, TBD.) into a new column.

This is the formula I've built but isn't working, where did I go wrong?


=IFERROR(Status@row), Status = "Sent"), "12. Done", IF(Date@row) = " ", "11.TBD", IF(Date@row) < TODAY(), "0. Overdue",IF(Date@row = TODAY(), "1. Today",IF(Date@row) < TODAY(),"days"), "2. Tomorrow",IF(WEEKNUMBER(Date@row),"Monday")=WEEKNUMBER(TODAY(),"Monday"),"3. This week",IF(WEEKNUMBER(Date@row),"Monday")=WEEKNUMBER(TODAY(),"Monday")+1,"4. Next week",IF(WEEKNUMBER(Date@row),"Monday")=WEEKNUMBER(TODAY(),"Monday")+2,"5. Two weeks",IF(WEEKNUMBER(Date@row),"Monday")=WEEKNUMBER(TODAY(),"Monday")+3,"6. Three weeks",IF(WEEKNUMBER(Date@row),"Monday")=WEEKNUMBER(TODAY(),"Monday")+4,"7. Four weeks",IF(WEEKNUMBER(Date@row),"Monday")=WEEKNUMBER(TODAY(),"Monday")+5,"8. Five weeks",IF(WEEKNUMBER(Date@row),"Monday")=WEEKNUMBER(TODAY(),"Monday")+6,"9. Six weeks",IF(WEEKNUMBER(Date@row),"Monday")>=WEEKNUMBER(TODAY(),"Monday")+7,"10. Seven or more weeks away",IF(YEAR(Date@row)>YEAR(TODAY()),IF(WEEKNUMBER(Date@row),"Monday")+52=WEEKNUMBER(TODAY(),"Monday")+1,"4. Next week",IF(WEEK(Date@row),"Monday")+52=WEEKNUMBER(TODAY(),"Monday")+2,"5. Two weeks",IF(WEEKNUMBER(Date@row),"Monday")+52=WEEKNUMBER(TODAY(),"Monday")+3,"6. Three weeks",IF(WEEKNUMBER(Date@row),"Monday")+52=WEEKNUMBER(TODAY(),"Monday")+4,"7. Four weeks",IF(WEEKNUMBER(Date@row),"Monday")+52=WEEKNUMBER(TODAY(),"Monday")+5,"8. Five weeks",IF(WEEKNUMBER(Date@row),"Monday")+52=WEEKNUMBER(TODAY(),"Monday")+6,"9. Six weeks",IF(WEEKNUMBER(Date@row),"Monday")+52>=WEEKNUMBER(TODAY(),"Monday")+7,"10. Seven or more weeks away")))))))))))))))))))))

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I found another closing parenthesis out of place. Ugh. Sorry.


    =IF(Status@row = "Sent", "12. Done", IF(Date@row = "", "11. TBD", IF(Date@row < TODAY(), "0. Overdue", IF(Date@row = TODAY(), "1. Today", IF(Date@row = TODAY(1), "2. Tomorrow", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0), "3. This Week", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 7, "4. Next Week", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 14, "5. Two Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 21, "6. Three Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 28, "7. Four Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 35, "8. Five Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 42, "9. Six Weeks", "10. Seven or more weeks away"))))))))))))

Answers

  • Becky Wilson
    Becky Wilson ✭✭✭✭✭

    I don't know the answer, but did you try building them one IF statement at a time? I find by doing this I can make sure I am good and find the point of failure.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this:


    =IF(Status@row = "Sent", "12. Done", IF(Date@row = "", "TBD", IF(Date@row) < TODAY(), "0. Overdue", IF(Date@row = TODAY(), "1. Today", IF(Date@row) = TODAY(1), "2. Tomorrow", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0), "3. This Week", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 7, "4. Next Week", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 14, "5. Two Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 21, "6. Three Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 28, "7. Four Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 35, "8. Five Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 42, "9. Six Weeks", "10. Seven or more weeks away")))))))))))

  • Thanks for taking the time to review, I gave that a try but got back #UNPARSEABLE

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What are your column names? Can you provide a screenshot of the formula in the sheet similar to the snippet below?


    image.png


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    And do you have a screenshot of the formula in the cell similar to how the formula is displayed in my screenshot?

  • I just recognized you created additional columns reflecting the timeline logic - may be a stupid question but would I need to make the additional columns with the timeline logic to get it to work? My assumption is no.

    I was hoping the results would pull where I have the column titled "Formula Test", it'll pull the results so I can use a report to group by that column.

    image.png


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I didn't create any additional columns. The only two columns referenced in my formula are the Status column and the Date column.


    I believe the error may be coming from an extra closing parenthesis tucked in where it shouldn't be. Try this:

    =IF(Status@row = "Sent", "12. Done", IF(Date@row = "", "11. TBD", IF(Date@row < TODAY(), "0. Overdue", IF(Date@row = TODAY(), "1. Today", IF(Date@row) = TODAY(1), "2. Tomorrow", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0), "3. This Week", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 7, "4. Next Week", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 14, "5. Two Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 21, "6. Three Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 28, "7. Four Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 35, "8. Five Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 42, "9. Six Weeks", "10. Seven or more weeks away")))))))))))

  • We're getting closer@Paul Newcome! It's pulling only for 12. Done, 11. TBD, and 0. Overdue but pulls incorrect argument for the remainder ones:

    image.png


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I found another closing parenthesis out of place. Ugh. Sorry.


    =IF(Status@row = "Sent", "12. Done", IF(Date@row = "", "11. TBD", IF(Date@row < TODAY(), "0. Overdue", IF(Date@row = TODAY(), "1. Today", IF(Date@row = TODAY(1), "2. Tomorrow", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0), "3. This Week", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 7, "4. Next Week", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 14, "5. Two Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 21, "6. Three Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 28, "7. Four Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 35, "8. Five Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 42, "9. Six Weeks", "10. Seven or more weeks away"))))))))))))

  • @Paul NewcomeThat worked! Thank you so so so much!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    SUCCESS!!! Glad we were able to get it sorted.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!
Hi @SadieW<\/a> <\/p>


<\/p>

Here are the formulas that you can try<\/p>

  1. Count of starts for an individual =COUNTIFS(Priority:Priroty, 1, CA:CA, \"Full name as it appears in the cell\")<\/li>
  2. Sum of contract value for an individual =SUMIFS([Contract value column]:[Contract value column], Priority:Priroty, 1, CA:CA, \"Full name as it appears in the cell\")<\/li><\/ol>"}]}},"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":112244,"type":"question","name":"Countifs formula","excerpt":"Hi Team reaching out for some help again, i need a countifs formula to count all the high priority actions (Priority column) that are currently either not started or ongoing (Status column) Thanks in advance","snippet":"Hi Team reaching out for some help again, i need a countifs formula to count all the high priority actions (Priority column) that are currently either not started or ongoing…","categoryID":322,"dateInserted":"2023-10-27T02:53:17+00:00","dateUpdated":null,"dateLastComment":"2023-10-27T04:03:23+00:00","insertUserID":158264,"insertUser":{"userID":158264,"name":"Hobo","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Hobo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZLW3ELEW8RGX\/nZR392EQUH89I.jpg","dateLastActive":"2023-10-27T08:27:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":158264,"lastUser":{"userID":158264,"name":"Hobo","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Hobo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZLW3ELEW8RGX\/nZR392EQUH89I.jpg","dateLastActive":"2023-10-27T08:27:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":16,"score":null,"hot":3396755800,"url":"https:\/\/community.smartsheet.com\/discussion\/112244\/countifs-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112244\/countifs-formula","format":"Rich","lastPost":{"discussionID":112244,"commentID":401963,"name":"Re: Countifs formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401963#Comment_401963","dateInserted":"2023-10-27T04:03:23+00:00","insertUserID":158264,"insertUser":{"userID":158264,"name":"Hobo","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Hobo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZLW3ELEW8RGX\/nZR392EQUH89I.jpg","dateLastActive":"2023-10-27T08:27:18+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\/MLV6R3XY3ELA\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/MLV6R3XY3ELA\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/MLV6R3XY3ELA\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/MLV6R3XY3ELA\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/MLV6R3XY3ELA\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/MLV6R3XY3ELA\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-27T09:32:13+00:00","dateAnswered":"2023-10-27T03:45:21+00:00","acceptedAnswers":[{"commentID":401961,"body":"

    Hi @Hobo<\/a> ,<\/p>


    <\/p>

    You can use this formula. The formula will count rows where priority is high and status is not completed.<\/p>


    <\/p>

    =COUNTIFS(Priority:Priority, \"High\", Status:Status, <> \"Completed\")<\/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":112197,"type":"question","name":"Method to check whether a Assessment Date has passed upon multiple cells within a row.","excerpt":"Good afternoon, We are currently using a smart sheet to track assessment bookings for different standards. We enter the assessment dates within the relevant cells for a particular standard (normally 3 assessments per standard, however are named differently which has resulted in a large number of columns which is…","snippet":"Good afternoon, We are currently using a smart sheet to track assessment bookings for different standards. We enter the assessment dates within the relevant cells for a particular…","categoryID":322,"dateInserted":"2023-10-26T13:07:42+00:00","dateUpdated":null,"dateLastComment":"2023-10-27T10:08:35+00:00","insertUserID":143323,"insertUser":{"userID":143323,"name":"Matthew Drake","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Matthew%20Drake","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T13:18:03+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":143323,"lastUser":{"userID":143323,"name":"Matthew Drake","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Matthew%20Drake","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T13:18:03+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":59,"score":null,"hot":3396731177,"url":"https:\/\/community.smartsheet.com\/discussion\/112197\/method-to-check-whether-a-assessment-date-has-passed-upon-multiple-cells-within-a-row","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112197\/method-to-check-whether-a-assessment-date-has-passed-upon-multiple-cells-within-a-row","format":"Rich","lastPost":{"discussionID":112197,"commentID":401973,"name":"Re: Method to check whether a Assessment Date has passed upon multiple cells within a row.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401973#Comment_401973","dateInserted":"2023-10-27T10:08:35+00:00","insertUserID":143323,"insertUser":{"userID":143323,"name":"Matthew Drake","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Matthew%20Drake","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T13:18:03+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-27T10:10:19+00:00","dateAnswered":"2023-10-26T14:43:35+00:00","acceptedAnswers":[{"commentID":401827,"body":"

    @Matthew Drake<\/a> <\/p>

    If you wanted to build a nested IF statement, you would add RYG ball Symbol column and use a formula something like this:<\/p>

    =IF(AND(TODAY() > [Knowledge\/SJT\/MCQ\/Skills Test Date & Time]@row, ISBLANK([Knowledge Test Result]@row)), \"Red\", IF(AND(TODAY() > [Project \/ Showcase Portfolio]@row, ISBLANK([Project \/ Showcase Portfolio Results]@row)), \"Red\", \"Green\"))<\/p>

    You could keep building this out to evaluate all of your date \/ result fields, but you could run into issues where a date is not yet entered.<\/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&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&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 in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

    Trending in Formulas and Functions