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.

Download the 2011 staff holiday planner calendar – Excel XLS (20kb zipped)

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

Download the 2011 staff holiday planner calendar with formulas – Excel XLS (21kb zipped)

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!

Dear Sir,

Very good work but not completed up to Dec. 2011

Hi Abraham,

The calendar does in fact go up to January 2012. However earlier versions of Excel will truncate the calendar at 256 columns which is of course less than the 365+ columns needed to view the whole year.

You’ll need at least Excel 2007 (or OpenOffice which is free!) to be able to see until the end of the year.

I’ll add a note to the post to make this clear.

Thanks for pointing this out.

Good sheet but when entering holidays and sorting by name or department they do not move with the search ie add a new name and sort the holidyas do not move with the name on the sheet?

Hi Jerry, You need to make sure Excel sorts the entire row, and not just the name column. On later versions of Excel (certainly Excel 2007) there is an option to ‘Expand the selection’ that you can choose. There are probably similar on other versions of Excel too.

Thanks for the prompt reply, yes it does sort ok on the rows, but when sorting names and roles it does not, so if I added in other employees I would have to insert a row and them in the correct order, seems to make the column sort redundant?

thanks again

Jerry

loved it!

Hi Jerry – If you are using Open Office you can sort by name and role by doing the following.

1. Select the entire set of rows you want to sort. Click on the row numbers on the left to select the range you want.

2. The select ‘Sort’ from the ‘Data’ menu.

3. Choose which columns (and in which order) you want to sort by. This will be columns A & B for my spreadsheet.

4. In the options tab make sure ‘Include Formats’ is selected.

5. Then press OK.

The procedure is identical for Microsoft Excel 2007 except that you don’t have to worry about the cell formats being included as it Excel copies them automatically for you.

Hi rml

This is exactly what I’ve been wanting to build myself. Thank you ever so much. Really great work!

I will try and tweak it a bit to have autofill/autoformat in the calendar in regards to different types of leave, absence and other.

This is great thanks. But what would you do in order to keep track of how many days each employee has requested against how many days they each have left over?

So if employee A has a total of 30 days and has requested 20 then we would see that he has 10 days left to take. If he then requests 15 days it would show an error or change colour (something..) to show he can only take 10 and not 15.

Thanks

Hi Alex, You’d need some formulas to do that. Perhaps next to the employee name you could have a new column giving the total number of holiday that year, and another one with holidays remaining.

The total holidays would be a hard coded number depending on how many you allocate the employee, and the remaining holidays would be calculated by subtracting the number of cells which contain the text ‘holiday’ from the total. If you allow half days to be taken then you could factor that into the calculation as well.

At some point in the next month or two, I might make an updated version of this spreadsheet with these formulas built in – but it shouldn’t be difficult to do if you try to do it yourself.

Awesomeness! Thanks for this :-) Just what i’d been looking for. Got some nice formula in there all working tickedy-boo!

many thanks! very simple yet useful.

Hi

When I try and open this after downloading I get a message that reads

Cannot open due to problems with contents some parts are missing or invalid

This programme is what i have been looking for

Any ideas what I can do??

Many thanks

Hi Tanya – are you getting that error when trying to open it in Excel? I’ve verified that the file on this site is fine. Maybe try re-downloading it. Also what version of Excel are you using? It has been tested on Excel 2007, and I know it will also work with Open Office.

Sorted.

I was being particularly stupid!!!!

Now to try and add formulas for substracting annual leave as I go along…..I might be back!

Tanya

Great planner by the way! Just what I was looking for!

I’m an amateur at Excel coding :( I was wondering if its possible to use a COUNTIF formula in all cells, in the form of a conditional format.

so for example; if all employees have 25 days holiday entitlement, there would be a column showing the number of days they have remaining on the left, then whenever they are sick, or on holiday, as soon as the cell satisfies the conditional format (in this case the words “sick” or “holiday” etc, it would take the number of days away from the total days left.

Sorry for the long winded explanation. I hope it makes sense.

Hi John, yes you can definitely do this – and I can see that it would be very useful. I may soon add a second version of the staff holiday planner with these formulas built in, but in the mean time here are some formulas to help you.

For these formulas to work you need to add two columns: add column C for the ‘allocated holidays’ and column D for ‘remaining holidays’. You might need to click insert whilst selecting column A to avoid messing up the formatting, and then copy column A/B back to the correct place (maybe paste special and then select paste values only).

If you have the spreadsheet set up like that these formulas should work. You manually enter the allocated days into column C and the formula goes into column D. In this case the formula is for cell D6. Once you have checked it is working you can copy it down to your other employee’s cells.

Note that these formulas count 2011 only (to column NF), even though the calendar extends into the first two weeks of 2012. If you need different behaviour you’ll have to modify the formulas.

Count holidays only=C6-(COUNTIF(F6:NF6,”=Holiday”))

Count holidays plus sick days=C6-(COUNTIF(F6:NF6,”=Holiday”)+COUNTIF(F6:NF6,”=Sick”))

Count holidays plus sick days (with new ‘Half’ holiday value which subtracts 0.5 days)=C6-(COUNTIF(F6:NF6,”=Holiday”)+COUNTIF(F6:NF6,”=Sick”)+(COUNTIF(F6:NF6,”=Half”)/2))

You are a life saver! I cannot thank you enough.

I was following your instructions on how to enter the code, and it was very similar to mine, which contained errors lol. I then saw that you have already uploaded a newer one with the formulas lol. I learned something new regardless.

Once again, thank you! :)

John

Hiya,

This looks very good, just had a quick question, so i am assuming that when a member of staff is sick etc i just enter it under the day, then does this automatically take a day off in column D?

Hi Sanj – the version on this site doesn’t deduct holidays if you enter the sick day – but in one of my comments a few above this one you’ll find a formula which will deduct sick days from the total. Hope this helps.

Hi,

I’m using bit’s of this to ‘improve’what have to use,

I enter the ‘dates’ of Holidays on a summary page ie;

5/3/2011 6/3/2011 (in seperate cells) is there a way to then

fill in the cells on the main planner for the relevant days ?

Thanks

Hi Steve – That’s a good idea. And I’m sure that what you want is technically possible. However I think it would be complicated to implement. I’d guess you would have to write a lot of lines of VBA code to get that working. Of course if anyone had an easy way to do it I’d be very interested in hearing!

If you want a 2012 version of this spreadsheet it is now here: https://www.reviewmylife.co.uk/blog/2011/04/11/2012-staff-holiday-planning-spreadsheet/

Great spread sheet, would you be able to set this for april to april instead of year end? Also would be nice to have public holidays deductable from the allocation. what do you think?

Hi Gary, If you want an April to April planner I suggest you copy the Jan-April section of the 2012 holiday planner (linked two comments above) into the 2011 spreadsheet.

You can certainly deduct public holidays from the allocation – you can modify one of the formulas on this page to count them in the same way that the formulas are counting other types of day.

Hi, thanks for yor prompt response to my query, I’m bit a novice on formulas so the advice is very helpful, great tool by the way. Again many thanks:)

Hi there,

Your 2011 spreadsheet calender was absolutely perfect. I couldn’t have asked for a better one!

Thanks so much! :)

Just noticed your 2012 calender. thank you for that too!

Absolutely BRILLIANT! Just saved me a day or two! Maybe have the months in tabs for the update? Otherwise is great, nice and simple and easy for unit staff to manage and report. THANKYOU!!

Hi

how can i do it so that bank holidays are taken off of the total amount of holiday days left?

Thanks

Hi Rachael – can you just enter the number of non-bank holiday days that each employee has? e.g. instead of entering 20 you could enter 12 (assuming there are 8 bank holidays).

hi this is fab will there be a 2012 version ? many thanks

Hi Nigel – the 2012 staff holiday planner is already up. <- Click the link to see it!

Absolutely brilliant. Exactly what I was looking for. I was in the middle of creating the world’s least functional spreadsheet when I discovered this. Thanks very much

phil

Is there any chance you could split the staff holiday planner from Jan-Jun and then Jul-Dec to download as separate docs? (For those who dont have at least excel 2007). This is a great planner. Thank you

Hi Sara, If you want the planner split into 2 for an older version of Excel you can download OpenOffice, split it, and then save both parts as an old format Excel file. Or even better try to get your company to upgrade :)