Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a list with date and quantity of items used


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.

share|improve this question
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
up vote 1 down vote accepted

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


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


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


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

share|improve this answer
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

You must log in to answer this question.

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