Today's Google Sheets trick - Highlighting a date range with conditional formatting

It is difficult enough remembering what day of the week it is, much less what the date was on Monday. Why not let computers help?

The Problem

Maybe you have a bunch of entries in a spreadsheet and you want to quickly find just the ones from this week. Not too bad, right?

This gets much more complicated if the dates are sorted by something other than the date; for example, if you have a bunch of consulting hours but have decided to sort them by client. How many hours do I need to bill Windler Group this week?

The Tool

I have recently found myself using a lot of Google Sheets’ Conditional Formatting, and this is a perfect example of something I didn’t think was possible, much less relatively easy.

For this particular sheet, this is the formula I am using:

// If today's year and week matches A1, return true
=AND(YEAR(TODAY())=YEAR($A1),WEEKNUM(TODAY())=WEEKNUM($A1))

There are some subtle tricks here, worth highlighting:

• Using $ in front of A1 instructs the formatting formula to always look at column A, irregardless of which column is being considered for formatting
• The formula uses row 1 because that’s where the range (A1:D1000) starts. If the range didn’t start with row 1, then number would need to be changed accordingly: for example, if the range were B10:F00, replace $A1 with $B10
• Including the TODAY() function means this forumla will just work next week/next month/next year, so I can handle my end of things and let the computer take care of the drudgery
• I prefer ISO-8601-formatted dates, but the same forumla will work with dates formatted however you choose

The Result

Ah, much better!