Solved: Date Ranges

[d4vem]

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

[geekgirlau]

Can you post a small, sanitised version of your workbook?

[d4vem]

Thanks for your interest.

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

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

[d4vem]

Thanks very much for the solution its exactly what I wanted.

[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))

[Bob Phillips]

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]

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.