How to Chart Your Success Through Life, Week By Week

calendar-prototype

 

Update (5/24/14): Thanks for all of the great feedback on this post! For your convenience, I’ve included starter templates at the end of this post for those of you who would like to have this system for yourself.

Update (1/3/17): I’ve been getting more requests for a life calendar that works by calendar year instead of years since birth. I’ll post a link to that spreadsheet at the bottom of the post. Thanks for all of the great questions!

For years now, I’ve been using a system called Key Lifestyle Indicators (KLIs) that I modeled after the idea of Key Performance Indicators in business.

My KLI definitions have changed over the years; here’s my current set-up (click the image to view the full size version):

klis

This system works very well for me, consistently reminding me where I’m slacking and where I’m succeeding. The best part: it requires less than 10 seconds per day to enter the necessary information (nowadays it’s simply 1s and 0s transformed into a moving average).

It wasn’t until this past week that I found a way to leverage this tracked data in a new, interesting way. The inspiration came from reading Wait But Why’s post on what your life looks like plotted out into weeks (hint: You have less than 4,500 weeks in your life to spend – how have you been using them?).

I loved the idea of a meaningful reminder of the limited number of weeks we have on this planet. Thankfully, Wait But Why offers some sweet calendars that allow you to track how you’re living out your weeks – either with hand-scribbled notes or via color coding.

I’ll probably pick up one of these awesome calendars some time in the near future, but I wondered if I could use my years of KLI data to graphically represent this without needing to create yet another place to track my progress through life.

This post describes how I took my KLI data and created a Wait-But-Why-styled life calendar that lives in Google Spreadsheets (or Excel, if that’s your thing). If you track similar data in a spreadsheet, this article should help you in creating a similar set-up (read Wait But Why’s post for a deeper understanding of why a life calendar is awesome).

 

Step 1: Prototype the end result

Here’s what I wanted my life calendar to look like when all was said and done:

calendar-prototype

Note how I keep untracked weeks in gray, but everything else is colored yellow, green, or red depending on how my KLIs looked that week. The goal was to have this automatically calculated for me, as manually-inputted tracking can get quite annoying. But in order for this to become automated, I needed to pull together all of the necessary data, which brings me to step 2…

 

Step 2: Survey the current datascape

The calendar prototype looked good, but the data I needed was siloed in different cells. In other words, because none of the data was rolled up by day or week, some intermediary data compilation was required before the calendar could pull what it needed.

Key Lifestyle Indicators

 

 

Step 3: Compile the calendar data

After surveying my data, I realized I needed to compile my data in two ways to transition to the life calendar:

  1. Daily score sums…
  2. …rolled up into weekly score sums

I ended up adding two additional columns into my KLI sheet (which I now keep hidden) to accomplish step 1. See below (click the image to see it in full size):

Key Lifestyle Indicators additional columns

I calculated the week of my life (Column L) using this formula: =ROUNDDOWN((CURRENT_DATE - DATE_I_WAS_BORN +1)/7). The +1 in that formula ensures that the calculation includes today’s date when rounding down to the current week.

From there, I summed up each day’s scores using the sum of columns B, C, D, and E. For example, for the day on row 50, the M50 cell contained =B50+C50+D50+E50. For step 2, I used a pivot table to keep an up-to-date weekly summary of my scores:

Life Calendar Pivot Table

It rolls up all of the scores by week using the two new columns I showed in the previous screenshot. With my daily scores summed and rolled up by week, it was time to link this data to my calendar prototype.

 

Step 4: Bring the calendar to life

This step was the most time intensive, so I broke into three main tasks:

This task involved a VLOOKUP formula to pull the right score based on week. To pull from the right place in the pivot table, I simply needed to calculate the current week and then do the VLOOKUP.

It’s a little heavy to look at, but it ended up looking like this: =VLOOKUP(rounddown((CURRENT_YEAR*365)/7 + WEEK_INDEX+1),'Weekly Scores'!$A:$B,2, False)

Life Calendar Pivot Table

Here’s a broader view with cell highlighting. Notice how it uses the Week Index and the current year to calculate the correct week of my life, and then runs the VLOOKUP based on that value:

Life Calendar Pivot Table

 

B. Handle errors

Although the VLOOKUP worked quite well, there were two errors I had to account for:

  1. The formula returns an error for future weeks that haven’t been calculated yet.
  2. A particular oddity with Google Spreadsheets was causing the VLOOKUP to lock the formula in place and make it uneditable (turns out this is a Google Spreadsheets bug when doing a VLOOKUP on pivot tables – hopefully this gets fixed soon).

To solve the first issue, it was rather easy. I wrapped the VLOOKUP in an IFERROR like so:

=IFERROR(VLOOKUP_GOES_HERE, "")

This made sure that the formula returned an empty string for future weeks, keeping the calendar clean and error-free.

To solve the second issue, I used a trick I found on Stack Exchange. The trick involves arbitrarily adding a +0 to your VLOOKUP calculation. This won’t skew the calculation, obviously, and for some reason keeps the cell formula editable.

Here’s what the VLOOKUP looked like with the +0 added in:

VLOOKUP(rounddown((CURRENT_YEAR*365)/7 + WEEK_INDEX+1),'Weekly Scores'!$A:$B,2, False)+0

Problem solved!

With both errors handled, the final formula looked like this:

=IFERROR(VLOOKUP(rounddown((CURRENT_YEAR*365)/7 + WEEK_INDEX+1),'Weekly Scores'!$A:$B,2, False)+0, "")

C. Set up conditional formatting rules

With the hard part out of the way, all I needed to do was make the cells color coded based on my score that week.

In my KLI system, the ideal day would earn me 4 points. Therefore, my ideal week would yield 28 points. With that in mind, here’s how I defined and color-coded great, ok, and bad weeks.

Life Calendar Pivot Table

As you can see, 20 points or above is a good week, 15-20 points is a so-so week, and anything less is a red flag. Also notice how I alter the text color as well to hide the number calculation that lives in the cell.

 

Step 5: Celebrate!

At this point, I took a step back and appreciated my sexy, new life calendar, auto-calculated based on the KLI system I’ve trusted for years.

Life Calendar Final Result

Tim Urban, if you’re reading this post, I just want to say thank you for the inspiration. I follow your blog religiously, and this obviously could not have come about without your wisdom. Cheers mate.

Now, off to appreciate the precious weeks I have left.

Update: By popular demand, I’ve put together a template you can simply copy into your own Google Docs account in under 2 minutes! All you need to do is click this link, and go to File –> Make a copy.

Update 2: If you’re looking for the calendar year version instead, go to this link.

Making a copy of the life calendar

From there, view the ‘Read Me’ tab on this spreadsheet to learn how to get it set up. I’ve automated nearly everything. All you need to do is input your birthday and then choose how you’d like to categorize the four lifestyle / habit fields you’re given.

And finally, thanks to those of you who offered to pay for the spreadsheet template – you’re too kind! Rather than charge for the template, I’ll simply add a donate button at the bottom of this post. If you’d be interested in donating a few dollars to help with hosting fees, feel free. (You’re under no obligation, of course!)

Questions? Feedback? Leave it in the comments below!


About Jon Guerrera

I'm Jon Guerrera, a life hacker at heart and the man behind the scenes here at Living For Improvement. This blog documents all of my successes, failures, and lessons learned as I experiment with finding happiness and fulfillment. I also wrote an e-book. If you like what I write on the blog, you can grab a free copy by subscribing.
  • Artur Dorovskikh

    Thanks!

  • Just a heads up, I used the calendar and the week of the year isn’t displaying properly. For example today is the 25th week of 2016 yet the “Life Calendar” tab shows it to be the 32nd week.

  • Thanks for the comment, Glenn! When is your birthday? The Life Calendar is oriented to your birth month, so in your example, I’m guessing the Life Calendar tab is showing it to be the 32nd week since your last birthday. Let me know if that’s not the case! -Jon

  • Ok. That makes sense. I was thinking too rigidly and not realizing your sheet accounted for the exact day. The KPI thing is a great idea. It’s a useful way to sit down at the end of the day and really evaluate if you are on track.

    I found it helpful to set up a scoring table so I have a concrete definition of what it takes to get a 1, 2, etc… .

    Great template! Thank you Jon.

  • Frugal Frequency Holder

    Just finished reading Tim Urban’s article on this subject, and my next impulse was to see if anyone had taken the time to create a Google Docs version of it. And you did. Thank you!

    One question: I’ve entered my birthday in cell A1. I’ve entered my start date (today, 8/11/2016) in cell A3. But the subsequent cells aren’t updating with tomorrow’s date and so on. Is this as designed?

  • Ah, great catch! You’ll need to expand the dates the good ol’ fashioned way, using the drag bar. See here: http://webapps.stackexchange.com/questions/30683/create-a-column-of-date-in-google-spreadsheets

    I’ve updated the spreadsheet’s instructions to reflect this step. Thanks for pointing it out!

  • ali

    Thanks a lot so I created
    Please share your file in Excel?!

  • Chris

    Thanks so much for this excel sheet. Extremely helpful for tracking my goals.