Help on a Date formula

[Guest]

Can anyone advise on te following:
Where:
Date A is input date
Month 1 (Jan) = x
Month 2 (Feb) = y
Month 3 (March) = z
& so on

Require formula to calculate as follows:
If date A = Jan then calculate x
If date A = Feb then calculate x+y
If date A = March then calculate x+y+z
and so on, required for a 24 month period
TTT

 

[David Biddulph]

Try =SUM(OFFSET(B2,0,0,12*(YEAR(A1)-2007)+MONTH(A1),1))
assuming that your input date is in A1, and your month 1 data value is in
B2 (and that month 1 applies to Jan 2007, so that your 24 month period
covers Jan 2007 to Dec 2008).
Adjust as necessary.

 

[Guest]

David

Tried this but has come up with a formula error, seems there is a problem
with the last statement ),1)); any ideas what the problem could be; also if I
change the date it does not pick up the accumaltive totals.

 

[David Biddulph]

No idea. It works fine for me.
Did you copy the formula into the formula bar, or did you retype it? Did
you make any changes? Copy the formula from the formula bar back here and
we'll have a look at it.

 

[Guest]

David

Formula is now accepted (must have been a typing error); thank-you

Still have a problem;

As follows:
Col1 Col2 Col3
Jan Feb March
£15 £15 £15

If I input 1/1/07 into a seperate cell (A1 from your formula) the finished
sum should equal £15 (B2 from your formula)
If I input 1/2/07 into a the A1 cell the calculation should equal £30
If I inut 1/3/07 into A1 the calculation should equla £45
and so on

Any ideas???

 

[David Biddulph]

You presumably didn't look in Excel help to see the syntax of the OFFSET
function?
If you've got the data in a row, rather than in a column, then change the
formula from =SUM(OFFSET(B2,0,0,12*(YEAR(A1)-2007)+MONTH(A1),1)) to
=SUM(OFFSET(B2,0,0,1,12*(YEAR(A1)-2007)+MONTH(A1)))

 

[Guest]

Cheers David

Works perfect; one last question how does it work if you want to start the
period half way into the year?

Yours
Trevor

 

[David Biddulph]

You've still not looked at the formula to see how it works? The term
12*(YEAR(A1)-2007)+MONTH(A1) is the one that gives the offset from January
2007, so you merely need to give it a different starting point.

 

[Guest]

David

I do have difficulty with date formula; could you show me how the formula
would look like for starting in Feb 07; and Feb 08.

This will hopefully allow me to get to grips with the formula nd allow me to
understand how it wrks

Thank-you for your patience

 

[David Biddulph]

Trevor,

Each of the functions used, such as YEAR() and MONTH() is a basic Excel
function, and each is described in Excel help, with examples, and usually
with "See also" links to similar functions.

To modify an old quote, most of us here are not in the business of just
giving you fish, but we are happy to teach you *how* to fish, in other words
we are happy to help you to help yourself if you wish to learn. If you
don't understand what part of a formula is doing, you can always break it up
into manageable chunks to see how each part behaves. Hence you can put
12*(YEAR(A1)-2007)+MONTH(A1) in a cell, and see how it reacts to your
changing input.