I just love Excel. I remember when I was introduced to pivot tables—it was like the clouds of data ignorance had parted to reveal golden-robed angels singing of cross-tabbed insights. And I wasn’t even drunk! Except on the beauty of Excel, of course.
Just recently I was looking at some daily performance data, and I decided I wanted to group it by week. To start I just applied the WEEKNUM formula:
=WEEKNUM(date)
which renders “6/22/11”, for example, into “26”.
This worked fine for grouping, but “26” doesn’t really mean anything intuitively to me. When I wanted to figure out why the numbers jumped in a particular week, I had to go back to the daily sheet, find the week number in question, and see what the associated dates were—then I could say, “Oh, yes, 9/2, that’s when we had a piece run in the Wall Street Journal.”
So instead I decided to create a label that would display the first and last days of the week in question. After a bit of tinkering I hit on using the WEEKDAY function, which delivers a number for the day of the week a date falls on. I figured I could use that number to calculate how far from the beginning or end of the week the date is, then subtract or add the right number of days to deliver the two numbers. I used TEXT to make them labels:
=TEXT(date-(WEEKDAY(date)-1),”MM/DD”)&”-“&TEXT(date+(7-WEEKDAY(date)),”MM/DD”)
This yielded an output like this:
| Date | Week |
| 6/22 | 06/19-06/25 |
| 6/23 | 06/19-06/25 |
| 6/24 | 06/19-06/25 |
| 6/25 | 06/19-06/25 |
| 6/26 | 06/26-07/02 |
| 6/27 | 06/26-07/02 |
| 6/28 | 06/26-07/02 |
| 6/29 | 06/26-07/02 |
| 6/30 | 06/26-07/02 |
| 7/1 | 06/26-07/02 |
| 7/2 | 06/26-07/02 |
| 7/3 | 07/03-07/09 |
I formatted the labels to include the “0” in front of single-digit numbers because otherwise they don’t sort properly—October (10) comes before February (2).
Did I mention that I did all this on a Saturday morning? And in fact considered it to be a bit of a treat to myself for doing some other work? Yep, I party hard.