Hi all, One of my colleagues has a need and if there is an Expert out there who can help, I will be very greatful:

John manages 20 staff and records their leave in an Excel 2010 spreadsheet. He indicates when a staff member has a day off by colour according to whether it is sick leave, annual leave, long service leave etc.

The first column lists the employee's names. All other columns list the date for every day of the last five years. (this is a very wide spreadsheet)

When Jimmy has a sick day on 20 July, John colours that cell green for sick leave. If Jimmy has a week's holiday, John highlights the cells on Jimmy's row for the week and fills them in blue for recreation leave.

John's questions are "how do I find out how many sick days Michael has had off in the last 365 days, how many days are left for him to take off and how many days of each type of leave has he taken thus far in the last 365 days?"

Now the catch is:
1. that the leave period does not go from 01 Jan to 31 Dec or 01 Jul to 30 Jun.
2. according to the Award they are employed under, the employees are allowed to have 20 days off within a 365 day period.
3. John may check this spread sheet everyday or once per week or many times a month depending on when staff request leave. So, the 365 day period starts again on the day he checks the spread sheet (or the day the employee requests leave)

If needed, John is willing to turn this spread sheet into a table so that data can be filtered (or whatever other work is required to obtain the answer to these questions).

It would be so good if there is an expert out there who can help.

I have an image of the spreadsheet which I am willing to share but I cannot upload it here (some kind of glitch I suppose) so I can email the example if required.

Thank you in advance.