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?
Excel: how to convert a table with daily dates to weeks
microsoft excelmicrosoft-excel-2007
Related Solutions
Try casting your date to datetime.
SQL Server's date is internally represented as a 3 byte integer whereas datetime is 8 bytes and presumably floating point. Excel uses a double to represent dates/times so SQL Server's datetime format might map across better than date.
Use a Pivot Table to group your data by dates.
Steps
Assuming your raw data looks something like this:
Highlight all of the data and on the Insert tab select PivotTable. From the PivotTable Field List on the right select the Date and X fields. In the Values box in the lower right select the Sum of X dropdown and choose Value Field Settings change to summarize by Count.
You should now have something like:
Now up on the ribbon select the Options tab in the PivotTable Tools section.
Select Group Selection in the Group section.
Now choose months in the dialog
And your data should look like
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).
First, sort your file in Ascending Date order and have your day column be in date format.
Then, insert a blank column (say, a new ColA) for the weeks and make it to date format.
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.
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.
Copy the formula all the way down.
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:
To undo this, right click on any of the dates in the pivot table, and select 'Ungroup.'