Google Sheets – Date Highlighting by Weeks with Starting Date and Cell Range

conditional formattingdateformulasgoogle sheetsgoogle-sheets-dates

I am trying to build a "life calendar" similar to this – https://labs.coruscantconsulting.co.uk/life/weeks/ – in Google Sheets.

I have the manually designed sheet finished, but want to add the highlight of the current week and have it automatically update.
The first week in the sheet contains the birth date.
Horizontally from this cell is 104 cells per year(52 weeks, with a "spacer cell" for readability).
Vertically from this is years + a "spacer cell" between each year for readability.

All the cells are mostly empty with background coloring. Some cells contain "major events" from the past. No data points can be calculated.

Is there any way to use conditional formatting to temporarily highlight the current week using for instance background color or cell borders, perhaps counting weeks since birthday horizontally (+ "spacer cells") and years since birthday vertically.
I am only throwing out a suggestion since I have no idea how to start this calculation.

It is important that the highlighting is only temporary since the years have a set background color already.

If this is not possible using conditional formatting, is there some other way I could read up on?

Best Answer

You will need to delete all formulas in spacer cells in row 2 and then use this custom formula:

=IF(D2=WEEKNUM(TODAY(),2),1)

0

or for the whole column:

=IF(D$2=WEEKNUM(TODAY(),2),1)

enter image description here