I need to track sales by hour, need help creating workbook.

I need to create a workbook to track sales by hour from 12 to 5, and for each hour, the sales that came from eating in or take out. I then need to compare those sales to last years.

I originally just had it set up like sheet "January" of the attached file. I would enter the sales separately for eating in and taking out for each day, and enter the sales for last year, and I would use formulas for each week to compare this year to last year.

I need to get a bit more technical now, I not only need to compare this years sales to last years sales on a weekly basis, but I now need to do it on a monthly basis, and quarterly basis.

The problem is that for each sheet, I have four weeks of seven days, and each sheet is supposed to be a different month.

Months don't always start on Monday, so I have to have some days from last month, and some days from the next month on the current month's sheet to fill out the days of the week.

This was fine for comparing week to week. But for month to month I cannot have those overflow days from the other months in this months sales.

I thought about doing just a huge table for the year, but it would have to be 3D (each day, each hour, and then for each hour, sales that came from eating in or take out).

What I need: a way to enter hourly sales from each day based on sales from eating in and taking out, and being able to compare it to lasts years sales on a weekly, monthly, and quarterly basis.

Sorry if this is hard to explain, but hopefully from looking at the attached workbook it will make sense.

Originally Posted by Domski

Hi,

My advice would be to forget about the nice layout for the time being.

Your data should all be laid out in a table on one worksheet with a column for the date/time of the measurement and then in the row any information relating it. You will find doing any analysis far simpler then as you will be able to crunch your data using tools like pivot tables.

You can then produce nicely formatted reports from that rather than the other way round.

Dom

I was going to set up a table, but am unsure exactly how to set it up to be able to crunch numbers based on the data.