Solved: Date Ranges

Printable View

07-26-2006, 11:19 AM
d4vem

Solved: Date Ranges

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:
07-27-2006, 12:39 AM
geekgirlau

Can you post a small, sanitised version of your workbook?
07-27-2006, 11:08 AM
d4vem

Thanks for your interest. :friends:

I have attached a truncated version with the logic explained within the attachment on Sheet 2
07-27-2006, 11:34 AM
Bob Phillips

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.
07-29-2006, 04:16 AM
d4vem

Thanks very much for the solution its exactly what I wanted.:yes
07-29-2006, 06:56 AM
Shazam

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))
07-29-2006, 07:13 AM
Bob Phillips

Quote:

Originally Posted by Shazam

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.
07-29-2006, 07:43 AM
Shazam

Quote:

Originally Posted by xld

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.