|
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. |
=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" wrote in message ... 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" wrote in message ... 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. |
| All times are GMT +1. The time now is 10:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com