I'm trying to do a formula in the "Health" column. Once we are within 30 days from the due date, the status turns "Yellow" and once it is within 7 days of the due date and past it, the status would be "Red". Any other status would be green. I'm at a lost to where even to begin even after viewing some of the videos. If you've seen this before or have experience can you point me in the right direction?
I made an assumption that your sheet also had a column (which I called Status) that would indicate if the task was Complete. Make sure you update this formula to your actual column name and edit my word 'Completed' to your actual wording. Note the wording is case sensitive.
Add this formula to your Health column. The ball will turn green if the Status is Complete, or if the Due Date is 30 d or more greater than Today. The ball will turn Red if the Due Date is within 7 d or less than today. Anything in between will turn to yellow.
If your Program list in Sheet 2 is written out to match the option in your form and entries in Sheet 2, then you can just reference those for look up:<\/p>
=COUNTIF({Task Request Range 1},=Program@row)<\/p>
Where \"Program\" is the actual column header you have the list Auto Doors, Baler, Compactors, etc.<\/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":111081,"type":"question","name":"Combining selected cells into one cell ignoring blanks?","excerpt":"Hello all, I am trying to combine cell into one cell and I have found a couple other community posts covering this issue. The only problem is that their solution only applies if you are trying to collect the entire row, I need specific row. Originally, I did this just by using JOIN and then selecting each cell, separated…","snippet":"Hello all, I am trying to combine cell into one cell and I have found a couple other community posts covering this issue. The only problem is that their solution only applies if…","categoryID":322,"dateInserted":"2023-10-02T20:25:16+00:00","dateUpdated":null,"dateLastComment":"2023-10-02T21:09:01+00:00","insertUserID":167760,"insertUser":{"userID":167760,"name":"toca1","title":"User","url":"https:\/\/community.smartsheet.com\/profile\/toca1","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-02T21:06:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":167760,"lastUser":{"userID":167760,"name":"toca1","title":"User","url":"https:\/\/community.smartsheet.com\/profile\/toca1","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-02T21:06:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":42,"score":null,"hot":3392562857,"url":"https:\/\/community.smartsheet.com\/discussion\/111081\/combining-selected-cells-into-one-cell-ignoring-blanks","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111081\/combining-selected-cells-into-one-cell-ignoring-blanks","format":"Rich","tagIDs":[254,587,590],"lastPost":{"discussionID":111081,"commentID":397984,"name":"Re: Combining selected cells into one cell ignoring blanks?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397984#Comment_397984","dateInserted":"2023-10-02T21:09:01+00:00","insertUserID":167760,"insertUser":{"userID":167760,"name":"toca1","title":"User","url":"https:\/\/community.smartsheet.com\/profile\/toca1","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-02T21:06:06+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-02T20:53:50+00:00","dateAnswered":"2023-10-02T20:52:20+00:00","acceptedAnswers":[{"commentID":397979,"body":"
