Three year rolling period

[kfwkiss]

Hi all and thanks in advance for your help. I need to somehow create a formula which will calculate a three year rolling period and show how much study leave some has left. Basically, the people I work for are entitled to 30 days study leave over a three year rolling period from the month they started - so for ease if they started in August, this would be backdated until August 2008 and all days from then foward would be counted.

I have set up a very very simple spreadsheet - in column A it has the date, Column B a description and in Column C I want Excel to decide whether it should count the day or not i.e. if it is out of this three year rolling period. The person I am calculating first has a start date of December. I am really stuck and this is compounded by them being able to book for a future date and I also need Excel to be able to include future dates in the total so I can see if it surpasses 30 days.

Not sure if this makes sense and your expertise would be appreciated.

Many thanks,

Kate

 

[jberwald]

Welcome to the board.

I am not really sure I understand, but take a look and see if this works for you.

With their starting date, 8/1/2011 in A1; 8/1/2014 in B1; and 30 in C1

I would continue to put the starting date they requested for study leave in column A. The number of days requested for that date in column B. And this formula in column C, which can be dragged. =if(A2<$B$1,C1-B2,"No")

This will tell you how many days are left for the 3 year period. If column C shows NO, that means it is out of this rolling 3 year period and needs to be placed in the next period. Hope this helps.

 

[kfwkiss]

Thank you Jberwald for your reply however unfortunately this isnt what I need as I dont think I explained myself! At the moment column A has the date of the study leave i.e 10/09/2009, 15/11/2011 etc, column B the study leave description and column C how many days taken.
Currently I have to manually work out how many days have been taken and when past leave can be reallocated for use in the three year rolling period based on start date i.e a day taken in March 2009 will only be free to use in March 2012. In addition the doctor can only have a maximum of ten days in one year or 30 days over this three year rolling period. I am beginning to think that this isnt possible. Many thanks again