+ Reply to Thread
Results 1 to 5 of 5

rolling time period

  1. #1
    JJC
    Guest

    rolling time period

    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.



  2. #2
    George Nicholson
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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.
    >
    >




  3. #3
    Peo Sjoblom
    Guest

    RE: rolling time period

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


  4. #4
    Peo Sjoblom
    Guest

    RE: rolling time period

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


  5. #5
    JJC
    Guest

    Re: rolling time period

    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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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.
    > >
    > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1