2

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?

Improve this question
3
  • can you share a copy of your sheet?
    – user0
    Jun 4, 2019 at 10:16
  • Sorry for the late reply. Have been offline for a while. Yes, I'll need to anonymise some personal details first. I think I'll have some hours off later this week to get that done.
    – Nixxen
    Jun 11, 2019 at 6:16
  • Here's the anonymised sheet: docs.google.com/spreadsheets/d/…
    – Nixxen
    Jun 12, 2019 at 6:07

1 Answer 1

Reset to default
1

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

Improve this answer
1
  • I've played with this for a bit, trying to get it to take the week of birth (Taken from date of birth in D4) into consideration - so that it highlights not the current week, but the current week since DOB. Not quite there yet, but I think the information you've given me will get me there once I manage to find the correct formula to add to it - and this is a nice starting point. Thank you!
    – Nixxen
    Jun 28, 2019 at 6:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.