Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a spreadsheet where I need to run averages of the information from month, week & day. Below is just part of the data I would need to break down.

  • Column A is the Date Assigned
  • Column B is $$ Requested
  • Column C is $$ Approved

I need formula to take the dates (MM/DD/YYYY) and sort it by week so I can then run averages and such.

How do I get Excel to determine weeks from the dates?

share|improve this question

A quick, easy solution (if your info is organized like you described) is to create a pivot table and let it do the work for you.

  1. Select your data (columns A:C) and choose Insert > Table.
  2. Create a Pivot Table from your data table (step 1).
  3. Add your Date Assigned field to the Row Labels box in the Pivot Table Field List.
  4. Right-click on a date in the Pivot Table and choose the Group option.
  5. In the Grouping Dialog Box, choose the level of grouping you want. To create weeks, you'll need to select Day, then set Number of Days to 7.
  6. Finally, add your summary data to the Values Box. Here you can choose to have your data in any format, including summarizing by Average, Min, Max, or Running totals-pretty much any aggregation you can think of.

The advantage of using the Table for your data is two-fold: first, any custom columns you add to the table auto-calc for the whole table and are also usable in the Pivot. Second, whenever you update your Data Table, the Pivot Table automatically updates with the new data (or changes to the existing).

EDIT: Instead of using the Pivot Table's grouping (which is less than ideal without an OLAP data source), you can easily modify your existing table to improve performance. In addition to your original three columns, you can add columns for any other date period (e.g. year, month, week) that calculates off of your intial date value. Just add these to your Pivot Table in order and you can roll up or down to the level of detail you'd like.

  • Column D =Year([@DateAssigned])
  • Column E =Month([@DateAssigned])
  • Column F =WeekNum([@DateAssigned])
  • Column G =Day([@DateAssigned])
share|improve this answer
Ok, let me try this – Kevin W. Jul 26 '12 at 11:51

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .