Split time in arbitrary periods, EXCEL

I have a list with date and quantity of items used

IE:

``` 2009.03.18 -1 2009.06.05 -2 2009.06.22 -1 2009.06.29 -2 2009.07.14 -1 2009.07.14 -1 2009.07.14 -2 2009.07.20 -2 2009.07.30 -1 2009.07.30 -1 2009.08.06 -1 2009.08.26 -1 2009.09.15 -1 2009.09.16 -2 2009.09.22 -2 2009.09.23 -2 2009.09.30 -2 2009.10.07 -1 2009.10.08 -2 2009.10.22 -1 2009.11.06 -3 2009.11.17 -2 2009.11.20 -1 2009.11.23 -2 2009.11.23 -1 2009.11.25 -2 2009.11.27 -1 2009.12.02 -2 ```

I need to know how much items i consumed in a determined period, ie, 15 days. I can do it in a monthly basis, basically using the month function to extract the month and work from there, but with an arbitrary time (which is the average lead time from my supplier) dont know how to get a function to split the date list in chunks of 15 (or whatever) days.

Any tips?

Thanks!

-
You could just split text to colums using the "." and the " "(space) as separators. From this, you can filter just the period that you need using the "Filter" option. Then you can use a funcition to count values. – Diogo Mar 28 '12 at 16:12
Do you want chunks of 15 days starting from the first date in the list? Or from some other date (e.g., the first of the month)? – Excellll Mar 29 '12 at 16:05

1 Answer

Is the quantity in a separate column? Assuming it is - with dates in column A and quantities in column B try this

Put the number of days for your period length in D1, e.g.15 then this formula in D3 for first date

`=MIN(A:A)`

and then this formula in D4 formatted in required date format and copied down as far as required

`=IF(D3="","",IF(D3+D\$1>MAX(A:A),"",D3+D\$1))`

that gives a list of start dates of 15 day periods (or whatever you define in D1)

Now for the quantity in that period use this formula in E3 copied down

`=IF(D3="","",SUMIF(A:A,">="&D3,B:B)-SUMIF(A:A,">="&D3+D\$1,B:B))`

You can change D1 to whatever period you want and dates and totals will automatically re-calculate.

The above assumes that the first 15 period starts with the earliest date in column A, if you want some other date instead then just manually enter that in D3 instead of the MIN function

-
just edited the above - some gremlins crept in to the original..... – barry houdini Mar 28 '12 at 17:30
Thank you Excel Houdini! (sorry, had to say it!) – Gabriel A. Zorrilla Apr 4 '12 at 17:49