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'm trying to see trends year to year in the number of meals served at our diner in order to predict staffing and ordering.

The best arrangement would be to compare a specific day on either side of a holiday. For example, how did we do on the Monday after thanksgiving in all past years.

I realize the holidays shift so it might be hard to set up, so I could also just compare the nth (name)day of the month. For instance, how did we do on the second Saturday of February in all previous years.

How can I do this?

share|improve this question
There's not enough information to answer the question. You haven't specified the layout of your excel spreadsheet or anything. – Simkill Jan 28 '13 at 16:14
I agree that you didn't give much to go on, but I answered anyways, because it's a fun question. – Doug Glancy Jan 29 '13 at 5:06

I imagine I'd use a pivot table. So, assuming my data was in a table that looks like this...

enter image description here

... here's how I'd set it up:

Column A: contains your dates

Column B: formula to extract the year - =YEAR([@Date])

Column C: formula to extract the month number: =MONTH([@Date])

Column D: formula to show the month name: =TEXT([@Date],"MMM")

Column E: formula to extract the day number: =WEEKDAY([@Date])

Column F: formula to show the day name: =TEXT([@Date],"DDDD")

Column G: formula to calculate the occcurrence of the week in that month and year:


Column H: contains your sales

Then make a pivot table from this table, and arrange it like this:

enter image description here

The result will look like this:

enter image description here

share|improve this answer

You must log in to answer this question.

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