2

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) don't know how to get a function to split the date list in chunks of 15 (or whatever) days.

Improve this question
2
  • 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
    Commented Mar 28, 2012 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
    Commented Mar 29, 2012 at 16:05

1 Answer 1

Reset to default
1

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

Improve this answer
1
  • just edited the above - some gremlins crept in to the original.....
    – barry houdini
    Commented Mar 28, 2012 at 17:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .