rolling time period

=SUMIF(A1:A100, ">=" & DATE(YEAR(TODAY()), MONTH(TODAY()) - $C$1, 1),
B1:B100)
Inputting 6, 12 or 18 into C1 changes the calculation to a rolling 6, 12 or
18 month.

You only need to adjust months. The Date function handle any crossover
between years for you.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"JJC" <JJC@discussions.microsoft.com> wrote in message
news:9E8F9BE1-B3C9-46F6-B47C-A592501A9E79@microsoft.com...
> In the following table in Excel; Colum A is the MMMM-YY format for dates
> and
> column B is my data. How do I get a rolling 6 month total, rolling 12
> month,
> and rolling 18 month total?
>
> January-02 2
> February-03 15
> March-03 0
> April-03 0
> May-03 0
> June-03 5
> July-03 6
> August-03 4
> September-03 0
> October-03 0
> November-03 2
> December-03 0
> January-04 0
> February-04 0
> March-04 0
> April-04 4
> May-04 5
> June-04 2
> July-04 0
> August-04 20
> September-04 10
> October-04 3
> November-04 5
> December-04 0
> January-05 0
> February-05 0
> March-05 0
> April-05 10
> May-05 0
> June-05 0
>
> I have used the following in the cell to come up with a yearly total
>
> =SUMIF(A1:A100,">=" &DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B1:B100)
>
> but am trying to get a 6 month and 18 month total. Also I am looking for
> data between Jan 04 and Dec 04 only.
>
> Any help would be appreciated.
>
>

First are you sure that the dates are numeric and if they are if they are
correct
if January-02 is supposed to be January 2002 then in the formula bar it
should display as 01/01/2002 or 01-01-2002 because if you type in January-02
it will default to Jan 2 2005, however if you type in any of the examples I
gave then you can use a custom format and get it to dsiplay as mmmm yy

for a 6 month it would look something like

=SUMPRODUCT(--(A1:A100>=DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))),--(A1:A100<=TODAY()),B1:B30)

Regards,

Peo Sjoblom

"JJC" wrote:

> In the following table in Excel; Colum A is the MMMM-YY format for dates and
> column B is my data. How do I get a rolling 6 month total, rolling 12 month,
> and rolling 18 month total?
>
> January-02 2
> February-03 15
> March-03 0
> April-03 0
> May-03 0
> June-03 5
> July-03 6
> August-03 4
> September-03 0
> October-03 0
> November-03 2
> December-03 0
> January-04 0
> February-04 0
> March-04 0
> April-04 4
> May-04 5
> June-04 2
> July-04 0
> August-04 20
> September-04 10
> October-04 3
> November-04 5
> December-04 0
> January-05 0
> February-05 0
> March-05 0
> April-05 10
> May-05 0
> June-05 0
>
> I have used the following in the cell to come up with a yearly total
>
> =SUMIF(A1:A100,">=" &DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B1:B100)
>
> but am trying to get a 6 month and 18 month total. Also I am looking for
> data between Jan 04 and Dec 04 only.
>
> Any help would be appreciated.
>
>

Sorry,

change the B1:B30 to B1:B100

Peo

"Peo Sjoblom" wrote:

> First are you sure that the dates are numeric and if they are if they are
> correct
> if January-02 is supposed to be January 2002 then in the formula bar it
> should display as 01/01/2002 or 01-01-2002 because if you type in January-02
> it will default to Jan 2 2005, however if you type in any of the examples I
> gave then you can use a custom format and get it to dsiplay as mmmm yy
>
> for a 6 month it would look something like
>
> =SUMPRODUCT(--(A1:A100>=DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))),--(A1:A100<=TODAY()),B1:B30)
>
> Regards,
>
> Peo Sjoblom
>
> "JJC" wrote:
>
> > In the following table in Excel; Colum A is the MMMM-YY format for dates and
> > column B is my data. How do I get a rolling 6 month total, rolling 12 month,
> > and rolling 18 month total?
> >
> > January-02 2
> > February-03 15
> > March-03 0
> > April-03 0
> > May-03 0
> > June-03 5
> > July-03 6
> > August-03 4
> > September-03 0
> > October-03 0
> > November-03 2
> > December-03 0
> > January-04 0
> > February-04 0
> > March-04 0
> > April-04 4
> > May-04 5
> > June-04 2
> > July-04 0
> > August-04 20
> > September-04 10
> > October-04 3
> > November-04 5
> > December-04 0
> > January-05 0
> > February-05 0
> > March-05 0
> > April-05 10
> > May-05 0
> > June-05 0
> >
> > I have used the following in the cell to come up with a yearly total
> >
> > =SUMIF(A1:A100,">=" &DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B1:B100)
> >
> > but am trying to get a 6 month and 18 month total. Also I am looking for
> > data between Jan 04 and Dec 04 only.
> >
> > Any help would be appreciated.
> >
> >

How do I just get the data between 10/03-11/04. For that formula I just need
my critera to be if >=10/03 and <=11/04, but I can't figure out that peice of
it.

The rest of it worked.

Thanks,

"George Nicholson" wrote:

>
> =SUMIF(A1:A100, ">=" & DATE(YEAR(TODAY()), MONTH(TODAY()) - $C$1, 1),
> B1:B100)
> Inputting 6, 12 or 18 into C1 changes the calculation to a rolling 6, 12 or
> 18 month.
>
> You only need to adjust months. The Date function handle any crossover
> between years for you.
>
> HTH,
> --
> George Nicholson
>
> Remove 'Junk' from return address.
>
>
> "JJC" <JJC@discussions.microsoft.com> wrote in message
> news:9E8F9BE1-B3C9-46F6-B47C-A592501A9E79@microsoft.com...
> > In the following table in Excel; Colum A is the MMMM-YY format for dates
> > and
> > column B is my data. How do I get a rolling 6 month total, rolling 12
> > month,
> > and rolling 18 month total?
> >
> > January-02 2
> > February-03 15
> > March-03 0
> > April-03 0
> > May-03 0
> > June-03 5
> > July-03 6
> > August-03 4
> > September-03 0
> > October-03 0
> > November-03 2
> > December-03 0
> > January-04 0
> > February-04 0
> > March-04 0
> > April-04 4
> > May-04 5
> > June-04 2
> > July-04 0
> > August-04 20
> > September-04 10
> > October-04 3
> > November-04 5
> > December-04 0
> > January-05 0
> > February-05 0
> > March-05 0
> > April-05 10
> > May-05 0
> > June-05 0
> >
> > I have used the following in the cell to come up with a yearly total
> >
> > =SUMIF(A1:A100,">=" &DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B1:B100)
> >
> > but am trying to get a 6 month and 18 month total. Also I am looking for
> > data between Jan 04 and Dec 04 only.
> >
> > Any help would be appreciated.
> >
> >
>
>
>

=SUMIF(A1:A100, ">=" & DATE(YEAR(TODAY()), MONTH(TODAY()) - $C$1, 1),
B1:B100)
Inputting 6, 12 or 18 into C1 changes the calculation to a rolling 6, 12 or
18 month.

You only need to adjust months. The Date function handle any crossover
between years for you.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"JJC" <JJC@discussions.microsoft.com> wrote in message
news:9E8F9BE1-B3C9-46F6-B47C-A592501A9E79@microsoft.com...
> In the following table in Excel; Colum A is the MMMM-YY format for dates
> and
> column B is my data. How do I get a rolling 6 month total, rolling 12
> month,
> and rolling 18 month total?
>
> January-02 2
> February-03 15
> March-03 0
> April-03 0
> May-03 0
> June-03 5
> July-03 6
> August-03 4
> September-03 0
> October-03 0
> November-03 2
> December-03 0
> January-04 0
> February-04 0
> March-04 0
> April-04 4
> May-04 5
> June-04 2
> July-04 0
> August-04 20
> September-04 10
> October-04 3
> November-04 5
> December-04 0
> January-05 0
> February-05 0
> March-05 0
> April-05 10
> May-05 0
> June-05 0
>
> I have used the following in the cell to come up with a yearly total
>
> =SUMIF(A1:A100,">=" &DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B1:B100)
>
> but am trying to get a 6 month and 18 month total. Also I am looking for
> data between Jan 04 and Dec 04 only.
>
> Any help would be appreciated.
>
>

First are you sure that the dates are numeric and if they are if they are
correct
if January-02 is supposed to be January 2002 then in the formula bar it
should display as 01/01/2002 or 01-01-2002 because if you type in January-02
it will default to Jan 2 2005, however if you type in any of the examples I
gave then you can use a custom format and get it to dsiplay as mmmm yy

for a 6 month it would look something like

=SUMPRODUCT(--(A1:A100>=DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))),--(A1:A100<=TODAY()),B1:B30)

Regards,

Peo Sjoblom

"JJC" wrote:

> In the following table in Excel; Colum A is the MMMM-YY format for dates and
> column B is my data. How do I get a rolling 6 month total, rolling 12 month,
> and rolling 18 month total?
>
> January-02 2
> February-03 15
> March-03 0
> April-03 0
> May-03 0
> June-03 5
> July-03 6
> August-03 4
> September-03 0
> October-03 0
> November-03 2
> December-03 0
> January-04 0
> February-04 0
> March-04 0
> April-04 4
> May-04 5
> June-04 2
> July-04 0
> August-04 20
> September-04 10
> October-04 3
> November-04 5
> December-04 0
> January-05 0
> February-05 0
> March-05 0
> April-05 10
> May-05 0
> June-05 0
>
> I have used the following in the cell to come up with a yearly total
>
> =SUMIF(A1:A100,">=" &DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B1:B100)
>
> but am trying to get a 6 month and 18 month total. Also I am looking for
> data between Jan 04 and Dec 04 only.
>
> Any help would be appreciated.
>
>

Sorry,

change the B1:B30 to B1:B100

Peo

"Peo Sjoblom" wrote:

> First are you sure that the dates are numeric and if they are if they are
> correct
> if January-02 is supposed to be January 2002 then in the formula bar it
> should display as 01/01/2002 or 01-01-2002 because if you type in January-02
> it will default to Jan 2 2005, however if you type in any of the examples I
> gave then you can use a custom format and get it to dsiplay as mmmm yy
>
> for a 6 month it would look something like
>
> =SUMPRODUCT(--(A1:A100>=DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))),--(A1:A100<=TODAY()),B1:B30)
>
> Regards,
>
> Peo Sjoblom
>
> "JJC" wrote:
>
> > In the following table in Excel; Colum A is the MMMM-YY format for dates and
> > column B is my data. How do I get a rolling 6 month total, rolling 12 month,
> > and rolling 18 month total?
> >
> > January-02 2
> > February-03 15
> > March-03 0
> > April-03 0
> > May-03 0
> > June-03 5
> > July-03 6
> > August-03 4
> > September-03 0
> > October-03 0
> > November-03 2
> > December-03 0
> > January-04 0
> > February-04 0
> > March-04 0
> > April-04 4
> > May-04 5
> > June-04 2
> > July-04 0
> > August-04 20
> > September-04 10
> > October-04 3
> > November-04 5
> > December-04 0
> > January-05 0
> > February-05 0
> > March-05 0
> > April-05 10
> > May-05 0
> > June-05 0
> >
> > I have used the following in the cell to come up with a yearly total
> >
> > =SUMIF(A1:A100,">=" &DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B1:B100)
> >
> > but am trying to get a 6 month and 18 month total. Also I am looking for
> > data between Jan 04 and Dec 04 only.
> >
> > Any help would be appreciated.
> >
> >

How do I just get the data between 10/03-11/04. For that formula I just need
my critera to be if >=10/03 and <=11/04, but I can't figure out that peice of
it.

The rest of it worked.

Thanks,

"George Nicholson" wrote:

>
> =SUMIF(A1:A100, ">=" & DATE(YEAR(TODAY()), MONTH(TODAY()) - $C$1, 1),
> B1:B100)
> Inputting 6, 12 or 18 into C1 changes the calculation to a rolling 6, 12 or
> 18 month.
>
> You only need to adjust months. The Date function handle any crossover
> between years for you.
>
> HTH,
> --
> George Nicholson
>
> Remove 'Junk' from return address.
>
>
> "JJC" <JJC@discussions.microsoft.com> wrote in message
> news:9E8F9BE1-B3C9-46F6-B47C-A592501A9E79@microsoft.com...
> > In the following table in Excel; Colum A is the MMMM-YY format for dates
> > and
> > column B is my data. How do I get a rolling 6 month total, rolling 12
> > month,
> > and rolling 18 month total?
> >
> > January-02 2
> > February-03 15
> > March-03 0
> > April-03 0
> > May-03 0
> > June-03 5
> > July-03 6
> > August-03 4
> > September-03 0
> > October-03 0
> > November-03 2
> > December-03 0
> > January-04 0
> > February-04 0
> > March-04 0
> > April-04 4
> > May-04 5
> > June-04 2
> > July-04 0
> > August-04 20
> > September-04 10
> > October-04 3
> > November-04 5
> > December-04 0
> > January-05 0
> > February-05 0
> > March-05 0
> > April-05 10
> > May-05 0
> > June-05 0
> >
> > I have used the following in the cell to come up with a yearly total
> >
> > =SUMIF(A1:A100,">=" &DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B1:B100)
> >
> > but am trying to get a 6 month and 18 month total. Also I am looking for
> > data between Jan 04 and Dec 04 only.
> >
> > Any help would be appreciated.
> >
> >
>
>
>

In the following table in Excel; Colum A is the MMMM-YY format for dates and
column B is my data. How do I get a rolling 6 month total, rolling 12 month,
and rolling 18 month total?

January-02 2
February-03 15
March-03 0
April-03 0
May-03 0
June-03 5
July-03 6
August-03 4
September-03 0
October-03 0
November-03 2
December-03 0
January-04 0
February-04 0
March-04 0
April-04 4
May-04 5
June-04 2
July-04 0
August-04 20
September-04 10
October-04 3
November-04 5
December-04 0
January-05 0
February-05 0
March-05 0
April-05 10
May-05 0
June-05 0

I have used the following in the cell to come up with a yearly total

=SUMIF(A1:A100,">=" &DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B1:B100)

but am trying to get a 6 month and 18 month total. Also I am looking for
data between Jan 04 and Dec 04 only.

Any help would be appreciated.

=SUMIF(A1:A100, ">=" & DATE(YEAR(TODAY()), MONTH(TODAY()) - $C$1, 1),
B1:B100)
Inputting 6, 12 or 18 into C1 changes the calculation to a rolling 6, 12 or
18 month.

You only need to adjust months. The Date function handle any crossover
between years for you.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"JJC" <JJC@discussions.microsoft.com> wrote in message
news:9E8F9BE1-B3C9-46F6-B47C-A592501A9E79@microsoft.com...
> In the following table in Excel; Colum A is the MMMM-YY format for dates
> and
> column B is my data. How do I get a rolling 6 month total, rolling 12
> month,
> and rolling 18 month total?
>
> January-02 2
> February-03 15
> March-03 0
> April-03 0
> May-03 0
> June-03 5
> July-03 6
> August-03 4
> September-03 0
> October-03 0
> November-03 2
> December-03 0
> January-04 0
> February-04 0
> March-04 0
> April-04 4
> May-04 5
> June-04 2
> July-04 0
> August-04 20
> September-04 10
> October-04 3
> November-04 5
> December-04 0
> January-05 0
> February-05 0
> March-05 0
> April-05 10
> May-05 0
> June-05 0
>
> I have used the following in the cell to come up with a yearly total
>
> =SUMIF(A1:A100,">=" &DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B1:B100)
>
> but am trying to get a 6 month and 18 month total. Also I am looking for
> data between Jan 04 and Dec 04 only.
>
> Any help would be appreciated.
>
>

First are you sure that the dates are numeric and if they are if they are
correct
if January-02 is supposed to be January 2002 then in the formula bar it
should display as 01/01/2002 or 01-01-2002 because if you type in January-02
it will default to Jan 2 2005, however if you type in any of the examples I
gave then you can use a custom format and get it to dsiplay as mmmm yy

for a 6 month it would look something like

=SUMPRODUCT(--(A1:A100>=DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))),--(A1:A100<=TODAY()),B1:B30)

Regards,

Peo Sjoblom

"JJC" wrote:

> In the following table in Excel; Colum A is the MMMM-YY format for dates and
> column B is my data. How do I get a rolling 6 month total, rolling 12 month,
> and rolling 18 month total?
>
> January-02 2
> February-03 15
> March-03 0
> April-03 0
> May-03 0
> June-03 5
> July-03 6
> August-03 4
> September-03 0
> October-03 0
> November-03 2
> December-03 0
> January-04 0
> February-04 0
> March-04 0
> April-04 4
> May-04 5
> June-04 2
> July-04 0
> August-04 20
> September-04 10
> October-04 3
> November-04 5
> December-04 0
> January-05 0
> February-05 0
> March-05 0
> April-05 10
> May-05 0
> June-05 0
>
> I have used the following in the cell to come up with a yearly total
>
> =SUMIF(A1:A100,">=" &DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B1:B100)
>
> but am trying to get a 6 month and 18 month total. Also I am looking for
> data between Jan 04 and Dec 04 only.
>
> Any help would be appreciated.
>
>

Sorry,

change the B1:B30 to B1:B100

Peo

"Peo Sjoblom" wrote:

> First are you sure that the dates are numeric and if they are if they are
> correct
> if January-02 is supposed to be January 2002 then in the formula bar it
> should display as 01/01/2002 or 01-01-2002 because if you type in January-02
> it will default to Jan 2 2005, however if you type in any of the examples I
> gave then you can use a custom format and get it to dsiplay as mmmm yy
>
> for a 6 month it would look something like
>
> =SUMPRODUCT(--(A1:A100>=DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))),--(A1:A100<=TODAY()),B1:B30)
>
> Regards,
>
> Peo Sjoblom
>
> "JJC" wrote:
>
> > In the following table in Excel; Colum A is the MMMM-YY format for dates and
> > column B is my data. How do I get a rolling 6 month total, rolling 12 month,
> > and rolling 18 month total?
> >
> > January-02 2
> > February-03 15
> > March-03 0
> > April-03 0
> > May-03 0
> > June-03 5
> > July-03 6
> > August-03 4
> > September-03 0
> > October-03 0
> > November-03 2
> > December-03 0
> > January-04 0
> > February-04 0
> > March-04 0
> > April-04 4
> > May-04 5
> > June-04 2
> > July-04 0
> > August-04 20
> > September-04 10
> > October-04 3
> > November-04 5
> > December-04 0
> > January-05 0
> > February-05 0
> > March-05 0
> > April-05 10
> > May-05 0
> > June-05 0
> >
> > I have used the following in the cell to come up with a yearly total
> >
> > =SUMIF(A1:A100,">=" &DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B1:B100)
> >
> > but am trying to get a 6 month and 18 month total. Also I am looking for
> > data between Jan 04 and Dec 04 only.
> >
> > Any help would be appreciated.
> >
> >

How do I just get the data between 10/03-11/04. For that formula I just need
my critera to be if >=10/03 and <=11/04, but I can't figure out that peice of
it.

The rest of it worked.

Thanks,

"George Nicholson" wrote:

>
> =SUMIF(A1:A100, ">=" & DATE(YEAR(TODAY()), MONTH(TODAY()) - $C$1, 1),
> B1:B100)
> Inputting 6, 12 or 18 into C1 changes the calculation to a rolling 6, 12 or
> 18 month.
>
> You only need to adjust months. The Date function handle any crossover
> between years for you.
>
> HTH,
> --
> George Nicholson
>
> Remove 'Junk' from return address.
>
>
> "JJC" <JJC@discussions.microsoft.com> wrote in message
> news:9E8F9BE1-B3C9-46F6-B47C-A592501A9E79@microsoft.com...
> > In the following table in Excel; Colum A is the MMMM-YY format for dates
> > and
> > column B is my data. How do I get a rolling 6 month total, rolling 12
> > month,
> > and rolling 18 month total?
> >
> > January-02 2
> > February-03 15
> > March-03 0
> > April-03 0
> > May-03 0
> > June-03 5
> > July-03 6
> > August-03 4
> > September-03 0
> > October-03 0
> > November-03 2
> > December-03 0
> > January-04 0
> > February-04 0
> > March-04 0
> > April-04 4
> > May-04 5
> > June-04 2
> > July-04 0
> > August-04 20
> > September-04 10
> > October-04 3
> > November-04 5
> > December-04 0
> > January-05 0
> > February-05 0
> > March-05 0
> > April-05 10
> > May-05 0
> > June-05 0
> >
> > I have used the following in the cell to come up with a yearly total
> >
> > =SUMIF(A1:A100,">=" &DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B1:B100)
> >
> > but am trying to get a 6 month and 18 month total. Also I am looking for
> > data between Jan 04 and Dec 04 only.
> >
> > Any help would be appreciated.
> >
> >
>
>
>