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

1. ## 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

2. ## Re: 12 month rolling period with variable dates over the 12 month period

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".

3. ## Re: 12 month rolling period with variable dates over the 12 month period

Hi Newdoverman

The counting backwards for four occurrences was my way of explaining that:

In a 12 month rolling period an employee can have a maximum of four half days - not full working days (as a perk) in their working day without having to make the time up or take holiday leave. So the spreadsheet I'm trying to make needs to be a rolling year counted backwards from the date of the last half day or expected half day (if half day planned in advance) at any moment in time - so it's always the date of 4 half days including the current or planned half day with the first half day in the list not counting with each new half day added as the time frame rolls forward. Basically the rolling element needs to be the first in the list of dates falling out of the equation when a new date is added (a future date) but its still got to be within a 12 month rolling period so that the number of half days doesn't exceed four over 12 months starting at the set date of the first time the option is exercised. The problem I have is that the dates are variable but must fit within a 12 month period. Hope that makes sense.

4. ## Re: 12 month rolling period with variable dates over the 12 month period

So the first half day taken was on 08/05/13, next on 22/05/13, next 19/07/13. That's three of the four allowed up to 08/05/14. The next booked in advance half day will be 03/03/14 (that will make four inside the 12 months from 08/05/13).

After this the next half day will be 14/04/14 (that technically makes five) during the preceding 12 months. So my spreadsheet should tell me that date will mean five half days (amount allowed exceeded)

The next half day will be 12/05/14 which moves out of the 12 month starting 08/05/13 but now I want the 08/05/13 to fall out of the equation and the next 12 month rolling period to start on 22/05/13 so that a max four days will run from 22/05/13 - 22/05/14 but as soon as I add 19/07/13 the 22/05/13 is dropped and the next rolling 12 months starts 19/07/13 and so on as new dates are added in the future.

Thanks

5. ## Re: 12 month rolling period with variable dates over the 12 month period

I am totally confused

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

6. ## Re: 12 month rolling period with variable dates over the 12 month period

I've attached the file.

Many thanks.

7. ## Re: 12 month rolling period with variable dates over the 12 month period

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.

8. ## Re: 12 month rolling period with variable dates over the 12 month period

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

9. ## Re: 12 month rolling period with variable dates over the 12 month period

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.

10. ## Re: 12 month rolling period with variable dates over the 12 month period

I'm happy to work in days

Thank you so much for your help.

11. ## Re: 12 month rolling period with variable dates over the 12 month period

Thanks for the feedback.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1