Help on a Date formula
 

Can anyone advise on te following:
Whe
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

Help on a Date formula
 

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.
--
David Biddulph

"tomttom40" wrote in message
...
Can anyone advise on te following:
Whe
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

Help on a Date formula
 

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


"David Biddulph" wrote:

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.
--
David Biddulph

"tomttom40" wrote in message
...
Can anyone advise on te following:
Whe
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

Help on a Date formula
 

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.
--
David Biddulph

"tomttom40" wrote in message
...
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.
--
TTT


"David Biddulph" wrote:

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.
--
David Biddulph

"tomttom40" wrote in message
...
Can anyone advise on te following:
Whe
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

Help on a Date formula
 

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???
--
TTT


"David Biddulph" wrote:

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.
--
David Biddulph

"tomttom40" wrote in message
...
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.
--
TTT


"David Biddulph" wrote:

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.
--
David Biddulph

"tomttom40" wrote in message
...
Can anyone advise on te following:
Whe
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

Help on a Date formula
 

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)))
--
David Biddulph

"tomttom40" wrote in message
...
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???
--
TTT


"David Biddulph" wrote:

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.
--
David Biddulph

"tomttom40" wrote in message
...
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.
--
TTT


"David Biddulph" wrote:

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.
--
David Biddulph

"tomttom40" wrote in message
...
Can anyone advise on te following:
Whe
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

Help on a Date formula
 

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
--
TTT


"David Biddulph" wrote:

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)))
--
David Biddulph

"tomttom40" wrote in message
...
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???
--
TTT


"David Biddulph" wrote:

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.
--
David Biddulph

"tomttom40" wrote in message
...
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.
--
TTT


"David Biddulph" wrote:

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.
--
David Biddulph

"tomttom40" wrote in message
...
Can anyone advise on te following:
Whe
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

Help on a Date formula
 

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.
--
David Biddulph

"tomttom40" wrote in message
...
Cheers David

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

"David Biddulph" wrote:

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)))
--
David Biddulph

"tomttom40" wrote in message
...
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???
--
TTT


"David Biddulph" wrote:

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.
--
David Biddulph

"tomttom40" wrote in message
...
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.
--
TTT


"David Biddulph" wrote:

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.
--
David Biddulph

"tomttom40" wrote in message
...
Can anyone advise on te following:
Whe
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

Help on a Date formula
 

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
--
TTT


"David Biddulph" wrote:

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.
--
David Biddulph

"tomttom40" wrote in message
...
Cheers David

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

"David Biddulph" wrote:

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)))
--
David Biddulph

"tomttom40" wrote in message
...
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???
--
TTT


"David Biddulph" wrote:

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.
--
David Biddulph

"tomttom40" wrote in message
...
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.
--
TTT


"David Biddulph" wrote:

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.
--
David Biddulph

"tomttom40" wrote in message
...
Can anyone advise on te following:
Whe
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

Help on a Date formula
 

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.
--
David Biddulph

"tomttom40" wrote in message
...
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
--
TTT


"David Biddulph" wrote:

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.
--
David Biddulph

"tomttom40" wrote in message
...
Cheers David

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

"David Biddulph" wrote:

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)))
--
David Biddulph

"tomttom40" wrote in message
...
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???
--
TTT


"David Biddulph" wrote:

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.
--
David Biddulph

"tomttom40" wrote in message
...
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.
--
TTT


"David Biddulph" wrote:

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.
--
David Biddulph

"tomttom40" wrote in message
...
Can anyone advise on te following:
Whe
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