# How do I create a year to year comparison of weekdays?

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?

-
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...

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

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:

``````=COUNTIFS(B\$1:B2,YEAR([@Date]),C\$1:C2,MONTH([@Date]),E\$1:E2,WEEKDAY([@Date]))
``````