12 month rolling period with variable dates over the 12 month period

Hi all, I'm new here, thank you for letting me join

I'm not really an experienced excel user so this issue has me stumped.

I've made a 12 month rolling period but its only working based on whole calendar months. I've done it like this:

1) For the first 12 months - track of the total events for each month.
2) At the end of 12 months - total the events for the year.
3) After the first 12 months - subtract the first month from the total and add the next month.

The above works fine, however the dates making up 12 months will vary e.g.:

Date 08/05/13 occurrence 1 (12 month rolling period starts here)
Date 22/05/13 occurrence 2
Date 19/07/13 occurrence 3
Date 03/03/14 occurrence 4
Date 14/04/14 occurrence 5 (5 events so far 12 months from 08/05/13 - 08/05/14)
Date 12/05/13 occurrence 6 (this would be 6 events but its after 08/05/14 so the calender needs to adjust and drop off row one 08/05/13 and start counting events from 22/05/13) and of course it has exceeded four events!

Hope the above makes sense.

All I'm trying to achieve is a rolling 12 months period counting backwards for four occurrences including the date of the current occurrence (no. 4) with the first event not counting as a new date is added below so in a 12 month rolling period I can chart that number of occurrences doesn't exceed four.

Cell A1 = date
Cell B1 = 1 (being the number of occurrences for that date)

Many thanks, Chris

Something like this might work for you. With the dates in column A and the amounts in column B this gives a running total for the current day and back 365 days.

Formula:

Please Login or Register  to view this content.

I don't understand the "counting backwards for four occurrences".

I am totally confused

Can you post a workbook with representative data highlighted so that the description can be associated with something real?

I think that I have something that might work. There were problems with a couple of dates that were impossible and that threw everything in the dumper for a while.

Some of my figures differ from yours. My calculations are in the grey column.

Hi Newdoverman

Thanks for your help, much appreciated.

I can see that your figures differ because in the grey column you have entries for when no leave was used because you have applied the "SUMPRODUCT" calculation for Columns A & B down to the last row of entries (row 65). On date 14/04/14 if leave is allowed that will take the running 12 months total to FIVE meaning that the employee can't book that as time off without exceeding the four days allowed.

Just so that I can get an idea of how you worked it out for me, could you confirm that to make it a 12 months rolling total that the calculation drops off the first entry (leave already taken) in the list, starts the 12 months again at the date of the next leave taken and then includes the last entry (leave planned)?

I can see how to add further date rows but what would happen if I added new rows (dates) from the top?

Will the formula still work if I change the dates e.g. the relevance to the equation isn't the actual date as such, just the cell that contains the date working with the cell in column B to give a result in Column D? The reason I ask is that when I added the dates I simply added calendar month dates to fill the cell, the actual leave date will vary as the year(s) roll on.

Thank you ever so much for your time and trouble.

Chris

The formula counts back 365 days from the date on the left. This is a rolling year on a daily basis. The totals that you see are the totals up to and including that date for the previous 365 days. The calculations that you see for dates in the future are the totals that have already been booked or taken for a year previous to that date.

If you want to work in months, that becomes a complicated matter because months vary in length from 28 days to 31 days. It is better to work in days.

Thanks for the feedback.