PDA

View Full Version : Solved: Date Ranges

d4vem
07-26-2006, 11:19 AM
Hi All,

I have a 2 sheets. The first sheet details values/amounts on a weekly to weekly basis ending on a Saturday. Each column contains the end of the week. Each row contains the amount for each week over a duration of 2 years (Yes thats 104weeks!!!! and columns). What I need to do is calculate the month of the last Saturday in each week (not bothered if the week crosses a month) and convert each week to a month on an 2 year basis in a seperate sheet by month.

If thats confusing then the example is

Sheet1
Column A through to AZ (weekly - 104weeks = 2 years)

Sheet2
Column A through to X (monthly - 1st month to 24th month)

The problem I am having is incorporating the year into the date for the look up

Thanks in advance for any assistance:beerchug:

geekgirlau
07-27-2006, 12:39 AM
Can you post a small, sanitised version of your workbook?

d4vem
07-27-2006, 11:08 AM

I have attached a truncated version with the logic explained within the attachment on Sheet 2

Bob Phillips
07-27-2006, 11:34 AM
Add this formula to Sheet2 cell B2, and copy down and across

=SUM(IF((Sheet1!\$A\$2:\$A\$10=Sheet2!\$A2)*(TEXT(Sheet1!\$B\$1:\$L\$1,"mmyyyy")=
TEXT(Sheet2!B\$1,"mmyyyy")),Sheet1!\$B\$2:\$L\$10))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.

d4vem
07-29-2006, 04:16 AM
Thanks very much for the solution its exactly what I wanted.:yes

Shazam
07-29-2006, 06:56 AM
If you like a non-array. Input formula in cell B2 copy across and copy down.

Just hit enter.

=SUMPRODUCT((Sheet1!\$A\$2:\$A\$10=\$A2)*(MONTH(Sheet1!\$B\$1:\$L\$1)=MONTH(B\$1))*(S heet1!\$B\$2:\$L\$10))

Bob Phillips
07-29-2006, 07:13 AM
If you like a non-array. Input formula in cell B2 copy across and copy down.

Just hit enter.

=SUMPRODUCT((Sheet1!\$A\$2:\$A\$10=\$A2)*(MONTH(Sheet1!\$B\$1:\$L\$1)=MONTH(B\$1))*(S heet1!\$B\$2:\$L\$10))

In true terms it is an array formula, it works on an array(s), it has the preformance implications of an array formula, it has the limitations of an arry formula, it just isn't array-entered.

Shazam
07-29-2006, 07:43 AM
In true terms it is an array formula, it works on an array(s), it has the preformance implications of an array formula, it has the limitations of an arry formula, it just isn't array-entered.

This your right. I should've said;

Non-CSE

My mistake.