How To Highlight The Current Weeks Row In A Google Spreadsheet

Question

I am creating a team roster for my team so we know who is playing each week, and who is not. To make things more visually pleasing, I want to automatically highlight the row of the current week, so we can easily determine which round we are up to. How do I do this on Google Spreadsheets?

Answer

To do this Google Spreadsheets, I will split the task into two steps:

1) Create a column that returns if the week is a past week, current week, or future week

2) Create a Conditional Formatting rule

Below is a summary of the table, formula and conditional formatting rule.

The first step is to return a value to determine if we are in the current week. Enter the below formula in each row:

=if(today()>C2,1,if(today()<C2–7,2,3))

C2 is the date that you are comparing against.

What the formula doing is this:

If today’s date is before the match date, show 1.

Therefore, any days that have past the date, it will assign the value of 1.

If the date of the match is after today’s date, check if today date is not within the next 7 days. If it is, show 2, if not, show 3.

Therefore, the formula will show 3 if it’s in the current week, and 2 if it’s not within coming week, but it is in the future.

Now we have that sorted, we need to apply the conditional formatting. To do this, highlight the entire table and select Format –> Conditional Formatting.

By default, “Apply to Range” will contain the selected range.

Next, select Format Cells if Custom Formula Is, and enter the formula =$I2=3 (where I2 is the formula cell we entered previously. The $ sign will apply the conditional formatting across the entire row. Selecting =3 shows that it is in the current week, as per the formula.

Repeat this for each conditional formatting rule/colour you wish, but change the value to 1 or 3 depending if you want the past week (1) or a future week (2).

Once you are done, your Google Spreadsheet will now automatically highlight the upcoming round and your next match.