2011 staff holiday planner

36 Comments on “2011 staff holiday planner”

1. Dear Sir,

   Very good work but not completed up to Dec. 2011

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

3. 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?

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

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

6. loved it!

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

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

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

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

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

12. many thanks! very simple yet useful.

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

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

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

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

17. 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))

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

19. 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?

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

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

22. 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!

23. 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/

24. 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?

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

26. 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:)

27. Hi there,
    Your 2011 spreadsheet calender was absolutely perfect. I couldn’t have asked for a better one!
    Thanks so much! :)

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

29. 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!!

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

31. 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).

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

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

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

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

36. 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 :)