Inspired by Your Life in Weeks, I wanted to create a life calendar for myself as well.
It is a pretty simple concept itself. However, I need to take care a few details:
- Through out my 90+ years life expectancy and present the time span in weeks, the calendar has 4680 cells. Most of cells are empty, especially during early years. The significant events in my life should be able to get spotted at a glance.
- Once spotted an event among the cells, I should easily locate when did it happen and which week did it happen in my life.
- I want to know which week I am currently in since I was born.
Constraining my engineering impulse for an app or website, I setup to develop the calendar with Google Spreadsheet.
- It is naturally format and align each cells nicely
- It is easy to edit, share, and control its access.
So pretty straight forward, rows represent my age, and columns represent weeks in each year. The first column is my birthday week :). I also decided to color code my life across a few significant time span, such as primary school, middle school, university, career, and retirement (expected) by filling the cells. Events are recorded as notes so that it is easily visible at grand level without clustered by the actual details. For special events such as new members in the family (my daughter and son), buying a house, or move to another city, besides the note on the cell, I would fill cells with yet a different color. Simple yet effective as how it looks like.
So far has been great to browse my history. However to show which week I am currently in I have to perform a bit of calculation first. I need a way to dynamically color code cells based on current date.
Conditional formatting is the tool comes to help. The basic idea is to fill the cell with color if my condition is TRUE. The formula I use is quite simple but with some twist.
First, I need to know how many weeks passed since my last birthday
Second, decide whether I should color the cell based on its column count
WEEKNUM(NOW(),1)–WEEKNUM(DATE($BB33,mm,dd),1)+WEEKNUM(DATE($BB33,12,31),1) = COLUMN()–1
Third, decide whether I should color the cell based on its row count
The final formula is
AND( WEEKNUM(NOW(),1)–WEEKNUM(DATE($BB33,mm,dd),1)+WEEKNUM(DATE($BB33,12,31),1) = COLUMN()–1, $BB32–$BB$2=YEAR(Now())-$BB$2+IF(NOW()>DATE($BB33,mm,dd),2,1)) )
Things looks great again except that some of my spreadsheet functions cannot be used with Custom formula under Conditional Formatting,
The work around is, put the formula in the cell itself, and use Text is exactly = TRUE as the condition. Then color all the text in cell as white.
And this is how my today’s week looks like.
Well, that’s it. The calendar is not perfect, but it serves my purpose of recording my life and give me a sense of urgency for future and a sense of hornor to be present. And it took me about two and half hours to set it up.