This is something I did years ago and the other day someone asked if I still had a copy I could let them have.
I think it's fun sometimes to understand how something works (I draw the line at cars - I just drive them!) and therefore...
Click the images for a larger view.
This is the spreadsheet as seen for a single user. The blue squares representing a day's leave have a value of 1, but you could make the text colour the same as the background so all you see is a square. Equations in the columns to the right add up the number of coloured squares for the month and then deduct it from your allocation.
For a manager to keep track of a team's annual leave it's a case of adding more rows for each month (a different colour for each team member can be good) and ensuring the equations match the relevant line as leave is taken month by month.
Then copy that down to B17:B25, B27:B36 etc. You will only have to enter staff names once now as it will automatically copy down from the first month into all other months.
Then copy that down to AI6:AI14, AI16:AI25 etc. It adds all the taken leave for the row.
Then copy down to AJ6:AJ14 only. It adds the initial leave allocation (AK5) plus any leave brought forward from the previous year (AL5) and then takes off any leave taken during the current month (AI5)
Then copy down to AJ17:AJ25, AJ27:AJ36 etc. It ensures that each month's remaining leave is based on the remaining allocation from the end of the preceding month.
Using the Spreadsheet
That's it! Fairly simple. Note that the coloured square in C1 has a value of 1 and the coloured and patterned square in C2 has a value of 0.5 to enable half day leave to be calculated.
When you add leave for anyone, copy and paste from either C1 or C2, rather than change the colour of cells to match - as that would not give them a value to be included in the calculations.
The coloured boxes for Sick Leave and Allowed Leave of Absence do not have a value as they would not be counted against annual leave allocations.
Every year you have to create a new blank and move the weekends (grey boxes) to match the dates of the new year.
Have a go! If you're desperate or have trouble getting it right, email me - bispham2[at]hotmail.com and I'll send it as an attachment.