Excel: how to convert a table with daily dates to weeks

microsoft excelmicrosoft-excel-2007

Have an excel file that tracks daily dates against a daily metric.

What I'd like to do is convert the table into weekly metrics. Is there an easy way to do this, perhaps with pivot tables?

Best Answer

Here's one way to do it using a helper column for Weeks, populating it with a formula to get the week, then pivoting by week (this works in Excel 2007 - I presume it would in 2010, as well).

  1. First, sort your file in Ascending Date order and have your day column be in date format.

  2. Then, insert a blank column (say, a new ColA) for the weeks and make it to date format.

  3. Next, type in the date for the week on which you want to begin (for instance, if your first day of data is 5/1/13, and you your weeks start on Sunday, then you'd type in 4/28/13). So let's just go with that and say this is cell A2 and you type 4/28/13 into it.

  4. Next, move down to Cell A3, and type in the formula to determine which week your day belongs to. Let's say your days are in Column B and data starts in row 2 (since row1 is headers). In A3, you'd type in: =if(B2>(A2+7),A2,(A2+7)). Don't worry about greater than or equal - Excel makes the assumption that's what you mean here.

  5. Copy the formula all the way down.

  6. Make the pivot with weeks in the rows.

And, to elaborate on the comments below, here's how to do it from within the pivot table itself without a helper column:

  • First, make the pivot table with the dates in the rows.
  • Then right click on any of the dates and select 'Group...'
  • A dialogue box pops up. The top 'auto' section shows the first and last dates in your piot table; this should be fine to leave as-is (unless you want to change those, as well). What you do need to change are the selections for 'By' and 'Number of Days.'
  • In the 'By' section, deselect whatever is already selected (mine comes up with Months by default), and then select 'Days'.
  • Then, set the 'Number of Days' to 7.


To undo this, right click on any of the dates in the pivot table, and select 'Ungroup.'

Related Question