Excel 2008 : Find columns for date range

[rjmarshall17]

Hi,

I'm sure something similar has been asked in the past, but I'm having a hard time finding it.

What I have is a spreadsheet that I use for keeping track of income and expenses. The columns are weekly (Sat-Fri) and labeled with the date of the Friday (payday). So for this year I start with 01/07/2011, the next column is 01/14/2011, etc. I just keep adding 7 to the previous column and format as a date...

What I then want to do is to sum all of the rows by quarter. So I'm looking for a way to get the address of the first and last columns in a quarter. I currently am using:

=SUM(INDIRECT(ADDRESS(ROW(),2,,,"Weekly")&":"&ADDRESS(ROW(),24)))

But I'd like to get away from the "hard coded" columns (2 and 24 above - BTW: "Weekly" is the sheet with the weekly income/expenses). That way I won't have to tweak the formula each year, just designate a year and use that in a date calculation to determine which column is the start and which is the end of the quarter, date(YEAR,3,31) for the end of March.

I'm thinking there should be a way to say: find the first column where the date is > "end of quarter" and use that as start column for the range and something similar to get the "end" of the quarter.

Any suggestions?

Thanks,

Rob

[MarvinP]

Hi Rob and welcome to the forum,

You need Pivot Tables to do this problem in an easy manner. You can group dates by quarter, month, day, etc. See http://www.contextures.com/xlPivot07.html for examples.

[rjmarshall17]

Hi MarvinP,

Thanks for the welcome. I'll take a look at Pivot Tables, but I'm curious...in order for a Pivot table to work, is it possible to have blank/empty cells in the range? The reason is that because this is for income/expenses there will, obviously, be weeks when no expense for a particular item (row) occurred. When I tried to set up a Pivot Table Report using some test data, it only gave me the dates (which were the only range of cells with no empty cells) as available for formatting/layout. I also didn't see anything that allowed me to say: group these columns into a quarter...but perhaps I haven't spent enough time looking at it, yet.

Thanks again,

Rob