I had a request to create an Excel staff holiday planner in addition to my usual one page calendars that you’ll find on this site. The Excel staff holiday calendar for 2011 can be used to note down holidays, training days, sick days, and home/off-site time at your company. Here’s an example of what it looks like with some information filled in.
Is has the whole of 2011 going left to right, and staff members can be listed from top to bottom. The weekends and months are coloured, and the days are labelled to make it easy to see where you are in the calendar. Week numbers are listed as well – I’m using the ISO 8601 week numbering convention if you are interested in such things.
I’ve frozen the top and left parts of the spreadsheet so they are always on screen. The staff role column has an auto-filter box in case you want to filter by job type. And on the top left (highlighted in the red box below) are a number of colour coded categories that you can easily copy and paste into the relevant cells. Change the category names, and colours to suit your taste.
To delete the colours in any cells just copy and paste an empty cell over it (Ctrl-C, Ctrl-V is the fastest way).
Here’s a final picture of the blank staff planner which you can download for free from the link underneath it.
Staff holiday planner with remaining holiday formulas
By popular demand I’ve created a second version of this spreadsheet. This one has built in formulas to calculate the remaining holiday. It has extra slots at columns C and D for allocated holiday, and remaining holiday. Column D counts cells containing the work ‘Holiday’ and ‘Half’ which subtract one day and half a day respectively from column C. If you look in the comments of this page you’ll see some other formulas as well for counting holidays only, and also for counting holidays and sick days as well.
I’ve set the XLS to count to the end of 2011 only, even though the holiday planner extends into the first few weeks of 2012. If you want different behaviour you’ll need to make some minor modifications.
If you have any suggestions for the 2012 version let me know in the comments below.
Note: You’ll need at least Excel 2007 (or OpenOffice which is free!) to view the whole year, as earlier versions of Excel limit the number of columns to 256. And we need 365+ columns to fit the whole year in!