I’m just starting out with conditional formatting in Google Sheets and need some help. I have a spreadsheet with project completion dates in one column. I want to set up rules that will automatically change the cell colors based on how close we are to each deadline. Specifically, I need the cells to turn different colors when the project end date is 60 days away, 30 days away, on the actual due date, or already overdue. Can someone walk me through how to create these conditional formatting rules? I’m not sure what formulas to use or how to set up the date comparisons properly.
hey! try using the TODAY() function in your formulas. like for 60 days: =A1-TODAY()<=60. for 30 days: =A1-TODAY()<=30. and for overdue: =A1<TODAY(). just change A1 with your date column when setting the rules.
Skip the default conditional formatting options - they won’t work for what you need. Set up four custom rules with these formulas: =AND($A1<TODAY()+60,$A1>=TODAY()+30) for 60-day alerts, =AND($A1<TODAY()+30,$A1>=TODAY()) for 30-day alerts, =$A1=TODAY() for due dates, and =$A1<TODAY() for overdue items. Replace $A1 with your actual date column. The AND functions prevent overlap between ranges, which would mess up your formatting. Test each rule separately to make sure the logic works.
Rule order is everything with date-based conditional formatting. Sheets applies the first match it finds, so go from most specific to least specific: overdue first, then due today, 30 days out, and finally 60 days out. I messed this up before and couldn’t figure out why my formatting was broken. Use absolute references when formatting cell ranges - otherwise your formulas shift and break. Select your whole date column first, then add the custom formula for each rule. TODAY() updates automatically so your colors change as dates get closer.