Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.

Grouping

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

share|improve this answer

The pivot table can also be built without a helper column. Group the dates by days and set them at 7 day intervals.

share|improve this answer
    
+1 MUCH neater (IMO) - but a helper column might be useful for the Expand/Collapse facility and week numbering might be preferred to the bandings. –  pnuts May 1 '13 at 9:52
    
Agree. The grouping will give row labels like "15/03/2013 - 22/03/2013". If week numbers are required instead, the helper column is the way to go. For a quick look at 7-day chunks, the grouping is the faster option to build. –  teylyn May 1 '13 at 10:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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