This staff holiday planning spreadsheet is a 2012 updated version of my 2011 staff holiday planner. This time the formulas for calculating the remaining holiday are in the default sheet – if you don’t need them you can just delete the columns.

As before the calendar planner shows the whole year left to right, with employee name down the side. As there are 365+ columns (for the days) you’ll need a spreadsheet package like Microsoft Office 2007 or OpenOffice to view the whole year. If you try to view it with a very old package like Microsoft Office 97 the year will get truncated after 256 columns.

Week numbers are listed according to ISO 8601. The top and side areas are frozen to make navigation easier, and there are some pre-prepared categories at the top left that you can copy and paste to the relevant cells (Ctrl-c, Ctrl-v are the shortcuts). Filtering for role and staff member name is set up as well.

Download 2012 staff holiday planner – XLS 22kb

The total number of holidays for the staff member can be entered into column C, and then column D automatically shows how many holiday days are left. You can replace the formula in column D if you want behaviour different to the default.

## Count full day holidays and half day holidays

This is the default formula for cell D6 which counts whole and half holiday days.

=C6-(COUNTIF(G6:NG6,”=Holiday”)+(COUNTIF(G6:NG6,”=Half”)/2))

## Count holidays only

If you want to use this formula paste into cell D6 and then copy down.

=C6-(COUNTIF(G6:NG6,”=Holiday”))

## Count holidays plus sick days

=C6-(COUNTIF(G6:NG6,”=Holiday”)+COUNTIF(G6:NG6,”=Sick”))

## Count holidays plus sick days (with new ‘Half’ holiday value which subtracts 0.5 days)

=C6-(COUNTIF(G6:NG6,”=Holiday”)+COUNTIF(G6:NG6,”=Sick”)+(COUNTIF(G6:NG6,”=Half”)/2))

**OpenOffice Err:508**

If you are using OpenOffice then you need to convert the commas in the above formulas to semi-colons. e.g. =C6-(COUNTIF(G6:NG6,”=Holiday”)) would change to =C6-(COUNTIF(G6:NG6;”=Holiday”)). Formulas that are in the default spreadsheet will work, this just applies to newly added formulas.

**If you like this post please consider linking to it using this code:**

is there anyway i can readjust the formulas to reduce hours instead of whole and half days? if so could u show me how–novice to excel–thanks

Frank

Hi Frank, yes you can do this.

1. Enter the number of hours holiday into column C.

2. Put this formula into D6 (and then copy it down to the other staff members).

=C6-(SUM(G6:NG6))

(Note that this range only counts the days in 2012, not the days that might be in the 2011/2013 cells.

3. Then enter the number of hours holiday being taken into each of the calendar cells as necessary.

Hi ,

Nice help….However was wondering how come there are no months after september in 2012..

Am i missing somthing .

Would help!!

Regards

Prakash

Hi Prakesh, Old spreadsheet packages (such as Excel 97) only support 256 columns which is why you might see a truncated version of the spreadsheet. To see the full year you will need to use a more modern package such as Excel 2007 or Open Office. Hope this helps.

hello!

We are a small not for profit business and your work is most valuable to us. Thank you so much

How can we thank you?

Greg

Hi Gregory, Thanks for your message. Glad you like the spreadsheets. If you want to show your appreciation you could do any of the following:

1. Link to one of the pages on the site.

2. Tweet about the page.

3. Like it on Facebook.

4. Recommend the site to a friend.

All of which are completely free! Cheers.

hey it is greg again

looks like the formula for holidays does not work with OpenOffice?

Please advise

thank you

Greg

Hi Greg, I’ve just tested the staff holiday planner spreadsheet in OpenOffice Calc 3.2.1, and the formulas works fine for me. Maybe if you gave me more details of the problem I could help. Thanks.

Hi!

I use Open Office

When I click on the following file “2011-staff-holiday-planner-with-formulas-blank” directly it does not work

Solution: Open The OpenOffice prog first then open the file

thanks

Hi Greg – the action that happens when you double-click on a file depends on the file associations that are set up on your computer. If you want XLS files to automatically open in OpenOffice Calc you can look at this page from the OpenOffice site.

is it possible to have a filter for each day that will not allow more than ‘x’ number of engineers for example to book off the same day, this number would need to be variable to suit job descriptions with larger or smaller quantities of employees

Hi Mike – I’ve had a look into how to do this. There is no (easy) way to actually limit the number of holidays booked, but it is not hard to add an indicator that will warn you if the limit is breached.

Here’s an example of how to do it using the default spreadsheet from this page – you may have to customise it for your use.

In cell D2 put a number which is the maximum number of holidays allowed each day. e.g. Put the number 3 in cell D2.

Then paste this formula into cell G20 (Monday 2nd January):

=IF($D$2-(COUNTIF(G$6:G19,”=Holiday”)+(COUNTIF(G$6:G19,”=Half”)/2))<0,"LIMIT!","")

This counts 'Holiday' and 'Half' cells and subtracts the total from the figure in D2. If the result is less than 0 it displays the text 'LIMIT!'.

Customise the formula for your needs, and then paste it across the whole row to the end of the year.

If you want to make the warning more obvious you could add some conditional formatting to highlight in red any cells that contain the text 'LIMIT!'. Hope this helps.

I’m a complete novice to excel. How do I get the 2011 holiday spreadsheet to show accrued holidays? I’ve only got a few employees who are allocated hols on a pro rata basis

Great spreadsheet by the way, has saved me endless hours of guesswork.

Thanks

Hi Trevor – showing accrued holiday is likely to need some much more complicated formulas. It might well be beyond my Excel skills to give you a solution, but I would be curious in knowing more about how you would see it working. e.g.

o Would you want the ‘Allocated’ to automatically update accrued holiday depending on the current date?

o Would you need a column to give details of how quickly the employee accrues holiday? e.g. 1 day per month.

Thank you for your reply. I think the answer to your two questions is “yes” on both counts. (automatically updated according to date and a column giving the amount of holiday accrued per month, which would be 1.6 days).As all employees are entitled to 28 days per year, I think I may be looking for an “actual” column to run alongside the “allocated” column. On reflection I can see how complicated this may prove to be, as the spreadsheet will need to take into account an employee’s start date in the calculation and not everyone starts a new job at the beginning of the month. Nor do they always start at the beginning of the holiday year, so the spreadsheet will need to calculate partial years.

My apologies in advance if I am causing you any headaches.

At the moment I have a spreadsheet that has conditional formatting – when a “H” is entered, it automatically highlights yellow and calculates the cell as 1.

There are a few people in the office that have annual leave on a hourly basis, so I am looking for a way of entering “H” and a number to calculate hours instead – is there a code/formula to do this?

Your help would be very much appreciated.

Thank-you,

Siân

Thank you so much for this, it will become a life saver for me. We have got into difficulties with holidays this year, and this will now save so much time for all concerned. Thank you once again, it is much appreciated.

Tara

Hi, thanks for this, it’s excellent, well done. However like Sian, some of our staff work in hours so I would need the spreadsheet to be able to calculate in days for some and hours for others and am not sure how to do this.

Thank you for your help.

Linda

Hi,

Firstly – this is fantastic! Well done, and thank you! I’m sure there are many organisations surviving holiday-headaches because of you! :)

I’m an absolute Excel novice, but learning from your formulas, I managed to amend the spreadsheet to account for half days off, whilst also account for when someone works a bank holiday, and is therefore due an additional day off! Quite proud of myself! :) Here’s the formula I used, incase you can think of a better tweak:

=B5-((COUNTIF(F5:NF5,”=Off”)+(COUNTIF(F5:NF5,”=AM Off”)/2)+(COUNTIF(F5:NF5,”=PM Off”)/2))-(COUNTIF(F5:NF5,”=Worked Hol”)))

Thanks again,

JK

Hi, fantastic spreadsheet. My company wants to on a seperate tab be able to pull through from the planner all employees who are off on todays date or any specific date, Is there any formula that can do this????

This spreadsheet has saved me hours of headache. Thanks

Hi No1_fraggle, there isn’t an easy way to do exactly what you want, but here is an alternative.

What you can do is to add autofilter to row 6 (don’t use it for an employee), and then for any date you can filter by whatever criteria you like.

e.g. for 15th September you could get a filtered list of all employees who have ‘holiday’ in their cell. You can then easily copy/paste a record of all the employees who have a holiday on that day.

To add autofilter to row 6 in Excel 2007 select row 6, and then click on ‘Filter’ in the ‘Data’ menu.

thats great you are a star, thank u, it was simple enough for the office staff to do. They have now asked if i can put the months into workbooks so they can have 12 workbooks but they want to be able to book holiday on each workbook and have the large planner automatically filled in aswell. being a novice i have tried and am still trying to do this, is there a simple way?

Hello – thanks for this its great, I’m finding thought that if I reorder the staff names, the allocated holodays dont move with them, ie the neames flip around but the holidays dont so they all allocate against the wrong person. I’ve just been cutting and pasting the holidat icon in. Is there a way around this?

Mnay thanks

Victoria

Hi No1_fraggle, you can certainly do that but you will need to know an Excel expert to set it up. There is a bit of information about this here: http://office.microsoft.com/en-us/excel-help/consolidate-data-in-multiple-worksheets-HP010095249.aspx

Hi Victoria, you need to select the whole row to ensure that the entire row is shifted. Here is how to do it in Microsoft Excel 2007.

Select from row 5 (the one with the headers of ‘Role’, ‘Name’ etc) up until the final row you wish to sort. Select the rows by clicking on the row number on the very left. You don’t need to click in the actual cells.

Then go to the Data tab and click on Sort. In the ‘Sort by’ field choose ‘Name’.

Click on ‘OK’ and then the whole set of selected rows will be sorted by name along with all the holiday data.

hey mate!

Our charity been using your spreadsheet and thanks again it is really helpful for not for profit companies like us who cant afford databases

I would like to suggest you a formula and another column where it would be possibel to see the numbers of sick days taken

Do you think this is possible?

Thank you

Gregory

Hi Gregory, it is very easy to set the calendar up to do this.

1. Right click on column D and choose Insert.

2. Then in cell D6 enter this formula:

=COUNTIF(H6:NH6,”=SICK”)

3. Copy this formula down into your other employees.

4. In cell D5 type ‘Sick’ for the title.

Note that as with the other formulas on this page the cell range just counts 2012 and not the extra days either side of it.

Hi there, Once again thanks very much for your hard work.

I need to be able to deduct public holidays from the allocated days off.

Can you provide me with a formula please and advise where to put it!

Thanks alot

Hi,

Please could you tell me how I could skip from month to month rather than having to scroll along a large spreadspreet?

hi there, i’m a mac user. is this the reason I cannot open the file? I downloaded and then opened the zip file which left 2012-staff-holiday-planner.xlsb.cpgz and 2012-staff-holiday-planner.xlsb but i cannot open either? Any advice/ ideas very welcome as am really keen to try out your excellent looking work, thanks in advance. best, nick

Hi Nick, ignore the .cpgz file – that is not in the zip, it was auto generated when the zip was unzipped.

The spreadsheet is the .xlsb file. What software are you using to open it? I’ve tested it with Microsoft Office 2007, and the latest OpenOffice.

Hi, great spreadsheet – i am findingh it very useful already. How easy is it to adjust the time period covered by the worksheet. Our holiday year runs from 1st April to March 31st.

Hi John, you could just copy Jan 1st – Mar 31st from the 2013 staff holiday planner into the 2012 one, and then delete the first three months. You might then have to adjust the formulas so that the ranges are correct.

Top marks for this, saved me a few hours work :)

S.

This spreadsheet is amazing, exactly what we need! However whenever i download the file it only lets me download a read only copy- not letting us add saff names etc? Im a total excel newbie- am i missing something very obvious!?

Thanks :-)

Hi confused, the staff holiday planner spreadsheet should be writable after downloading, however if your computer has decided to make it read only the easiest way around this is simple to save the spreadsheet into a new file – just choose save/save as from your spreadsheet application. Your new version should be writable.

Alternatively you can check if the original downloaded version has had the read only attribute set by viewing the file properties (usually by right clicking on the file).

Brill, just did that and its letting me edit it now! :-) Thanks so much. Another very silly question now.. how do we put in the holidays so that the ‘days remaining’ subtracts audotmatically!? Ie what do we type in the cell on the days off?

Hi confused, don’t type anything – just copy and paste the red ‘Holiday’ cell into the correct place and the days remaining will adjust automatically.

(Ctrl-C = copy, Ctrl-v = paste)

Thanks so much! :-)

Great link just what I needed as a previous poster is there a way of contracting the weeks to smaller cells for easier viewing?

Hi rpc, You can either zoom out a bit (probably in the view menu) which will reduce the size of everything, or reduce the width of those specific cells. e.g. try 80%.

On Excel 2007 you’d select all the columns, right click, and choose ‘Column Width…’. Or select all the columns and then put your pointer between the cell boundary and then drag the width to what you want.

You can do the same for the cell heights.

Hi! Thanks for this! It has been a great help!

I need to deduct public holidays from the allocated days off. Can you please give me a formula to do this?

Thanks

Hi RS, As public holidays are fixed would it be easier just to deduct the number of public holidays from the allocated days?

Or you could do something like this and use a ‘Public’ tag for each public holiday the member of staff is allowed.

=C6-(COUNTIF(G6:NG6,”=Holiday”)+COUNTIF(G6:NG6,”=Public”))

wonderful.. its helps a lot.. but i would like to add ‘sick column’ as well ‘allocate’ and ‘remain column’ instead of ‘role column’.. so please assist me to make a formula to calculate total sick.. plz advise..

Hi Nitha, This will count the number of sick days:

=COUNTIF(G6:NG6,”=Sick”)

You might have to slightly adjust the cell ranges if you are adding or removing columns.

Hi, can you please let me know if there is a version available for excel 2003 as our company is not going to invest in office 10 for several years yet & I have been given the unenviable task of managing the holidays for over 75 tasks. Thanks in advance for your time.

Hi Smcd666, the problem with Excel 2003 is that it only supports 256 columns, which is of course less that the number of days in the year.

You could use OpenOffice to split the calendar into two halves for the year. That is the only solution I can suggest for getting this to work with Excel 2003.

Hi, This is absolutely brilliant! But how am i able to include weekends within the holiday count. Please help. Thank you.

Hi Gigglez, any ‘holiday’ tags that are added to the weekends will be counted and deducted from the total. Did you try it?

hi, I just came across this very helpfull tool.

Please wht do i do to get authomatic upadate in remaining colunm when i paste holiday or sick days?

Hi Solo, Sounds like you have macros disabled. When you loaded the XLS was there a message telling you macros were disabled? If so you should enable them.