Google Sheets – Conditional Formatting to Filter Past Week’s Rows

conditional formattinggoogle sheetsworksheet-function

I have a running calendar in Sheets and I want last week's rows to disappear all at once every Monday (Sunday night at 11:59 ideally).

I've been trying to use conditional formatting to highlight all rows from last week, and then I can filter them out.

Here is a picture of my calendar, and I want rows <11 gone
I've been able to highlight any past Sundays red using the formula =AND($B1="Sunday",$C1<TODAY()), maybe this is a good start.

I've been playing around with this for a while and I'm wondering if it's even possible/curious to see how you'd do it.

Best Answer

You want last week's rows to disappear all at once every Monday (Sunday night at 11:59 ideally). You use formatting (and possibly conditional formatting) within your "calendar" (for example, 24 December is highlighted in green).

There are likely to be many ways in which your question could be answered. I have prioritised three factors:

1 - Any/all data formatting must be included in the output
2 - No on-going maintenance
3 - Avoid a script unless completely necessary.

You suggest that conditional formatting might be part of a solution. Perhaps it might, though I am sceptical; in any event, I will leave that for someone else to explore.

The answer requires some modification to the spreadsheet.

Step 1

  • Unmerge cells A1, B1, C1, D1, E1, F1 and create the layout and content as shown in the image and here:
    • Cell A1: Todays date->
    • Cell B1: =today()
    • Cell C1: 'Week->'
    • Cell D1: =weeknum(C1,2)

Step 2

  • Column A, CellA3: =WEEKNUM(C3,2)
    • copy this down as many rows of data in the calendar. If you add new rows, include this cell when you drag the last row to create new rows.

Step 3

  • Create a filter:
    • select the range A2:F1000 - IMPORTANT: extend the depth of the range to the last row of the spreadsheet OR as many rows as you think that your calendar will occupy.
    • From the main menu, select Data, Create a filter.
      • Click the Filter icon in Cell A2.
      • Click Filter by Condition
      • Select Custom Formula is
      • Enter =(A3>=$E$1)
      • Click OK

Filter - Sample Snapshot

Sample

My sample data runs from week 49 to week 52. In the snapshot, I have "forced" an earlier "current" date to demonstrate how the filter drops off earlier weeks and displays later weeks.

Related Topic