Date-based conditional formatting in Google Sheets

Sometimes a “real” project management tool is too heavy. And spreadsheets may be the most-abused software tool. So if you want to track the status of some tasks, you might want to drop them into a Google spreadsheet.

You have a spreadsheet with four columns: task, due, completed, and owner. For each row, you want that row to be formatted strikethrough if it’s complete, highlighted in yellow if it’s due today, and highlighted in red if it’s overdue. You could write conditional formatting rules for each row individually, but that sounds painful. Instead, we’ll use a custom formula.

For each of the following rules, apply them to A2:D.

The first rule will strike out completed items. We’ll base this on whether or not column C (completed) has content. The custom formula is =$C:$C<>"". Set the formatting style to Custom, clear the color fill, and select strikethrough.

The second rule will highlight overdue tasks. We only want to highlight incomplete overdue tasks. If it’s done, we stop caring if it was done on time. So we need to check that the due date (column B) is after today and that the completion date (column C) is blank. The rule to use here is =AND($C:$C="",$B:$B<today(),$A:$A<>""). Here, you can select the “Red highlight” style.

Lastly, we need to highlight the tasks due today. Like with the overdue tasks, we only care if they’re not done.=AND($C:$C="",$B:$B=today(),$A:$A<>""). This time, use the “Yellow highlight” style.

And that’s it. You can fill in as many tasks as you’d like and get the color coding populated automatically. I created an example sheet for reference.

3 thoughts on “Date-based conditional formatting in Google Sheets

  1. Hi, is it possible to highlight birthdays within 30 days before today in google sheets. I have been looking to highlight upcoming patients birthday within 30 days before birthdate. I have a column C with birth dates that needs to be highlighted if is: Today 7 days before 30 days before The idea behind this picture is to: Acknowledge the Birthday. Send to all our patients, a birthday card. Call the patient on his birthday. Here is a link to the sheet. Thanks for the help.

  2. Hi Jaime, there are two approaches you can use, assuming the dates don’t have years.

    One is to have another column that calculates the days until their birthday: =C1-TODAY()

    The other is to use conditional formatting like above. The rule that I think will work and handle birthdays near the new year is: =AND((C1-30)<today(),today()<C1)

    If the dates do have years, you will need to make it a little more complicated. Essentially, replace C1 above with date(year(today()),month(C1),day(C1))

Leave a Reply

Your email address will not be published.