2016 and 2017 staff holiday Excel planner and one page Excel calendar

The requests for the 2016 excel staff holiday planning spreadsheet have been coming in so I have made it as well 2017 one. Also I’ve uploaded the 2016/2017 one page excel calendars.

Staff holiday planner

First up here are my free spreadsheet that can help you track staff leave/holiday, training, sick days, offsite days, etc.

2016 2017 staff holiday planner

They come set up with formulas that can automatically track the total number of holiday days remaining (columns C&D) for each employee. If you don’t want to use this simply delete those columns.
The default formula subtracts 1 or 0.5 from the remaining holiday if the employee cells contain the words ‘holiday’ or ‘half. You can copy and paste (ctrl-c, ctrl-v) the coloured tags (holiday, half, training, etc) from the top left into the staff planning cells and the totals are automatically updated.

Note that the formulas measure the tags from the very start column (which is the year before) to the very end column (which is the year after).

See the 2012 planner for some customisation examples.

The week numbers are based on ISO 8601, and the top and left parts of the staff planner are frozen so you can always see them – this makes navigation easy. Here is the download link:

Download 2016 staff holiday planner spreadsheet – 22kb
Download 2017 staff holiday planner spreadsheet – 22kb

As the staff holiday planner has over 365 columns you won’t see the whole year if you open it with an old spreadsheet package such as Excel 97. You’ll need to use a more modern spreadsheet package such as Excel 2007 or LibreOffice (which is free). One workaround if you are forced to use Excel 97 at work is for you to use LibreOffice at home to split the spreadsheet into 2, one part for H1, and one for H2.

One page Excel calendar

I’ve put the whole year in this one page Excel spreadsheet.
2016 2017 one page excel calendar

You should be able to load the XLS using any version of Microsoft Excel from 97 onwards, or using the free OpenOffice or LibreOffice.

2016 one page Excel calendar – 13kb
2017 one page Excel calendar – 13kb

The one page Excel spreadsheet calendars and the staff holiday planners are zipped up. If your OS won’t automatically open the zip files you can download the free 7-Zip to unzip them for you.

Tags: , , , , , , ,

30 Responses to “2016 and 2017 staff holiday Excel planner and one page Excel calendar”

  1. Paul Burnham says:


    Many thanks for your great holiday spreadsheet. Can you please advise me on how to delete a booked holiday, I have tried but I find the total does not change when I delete the cell.


  2. Lisa Chandler says:

    thanks so much for your planner, you save me so much time every year & it’s much appreciated

  3. walt says:

    Just want to say many thanks for your holiday spreadsheet , Brliant

  4. IGor says:

    brilliant tool – very useful – is there any way to send you a small donation for keeping up good work, add to your beer fund or something?



  5. Steve Adams says:

    Thanks for the Holiday spreadsheet it’s very useful and has saved me hours of setting up

  6. Paul says:

    Thanks for the holiday planner

  7. aron says:

    Simple and highly effective, I have shared this calendar with other managers. Really appreciated, thanks for 2016, until next year

  8. Stuart Owens says:

    Just want to say many thanks for the holiday planner spreadsheet. Great job and saved me so much time. Much appreciated!!!

  9. Sylvia says:

    Hi,I don’t have much Excel experience so I haven’t fathomed out how to deduct holidays automatically. e.g. 20 days allocated, use 5 (entered on sheet) but it doesn’t deduct from total left. Some help would be appreciated! Many thanks.

    PS Last year’s seemed to do it automatically.

  10. Jonathan says:

    If anyone is interested I created a Report sheet which uses a validation lisst based on the employee names on calendar sheet say cell D3.

    Use the array below to paste in to a cell i.e. A8 (make sure A8:A30 the cell format is set to custom “mm dddd yy” and user shift & Ctrl & enter to store it, then drag the cell down, say about 30 cells deep.

    This will return the dates that someone was off for, you can do the same for any ‘reason’ for being off, just change the text in the middle of the forumal and re-save it with shift & Ctrl & enter drag the cells again to populate other cells.

    I named the range for employee names emp_name to make it easier to add new people without needing to redo all the formulas and also renames the main sheet to “2016”

    you can end up with a decent report page for an employee which can then be printed.


  11. gurdeep singh says:

    thank you

  12. Lizard says:

    Dude – fantastic work! Many thanks for sharing it, fair play to you!

  13. Dave Robbins says:

    Superb holiday planner. Most helpful. Cheers.

  14. Robert Halse says:

    Many thanks for your One page excel calendar most useful

  15. Dave B says:

    So much quicker than making this myself. Even imports into Google sheets nice and easily. Much thanks

  16. Andrea Wallace says:

    This is fantastic………….will hopefully help me not to mess AL up Thanks

  17. Simon says:

    Thank you so much much for your hard work. Is there a way that instead of placing ‘holiday’ one could enter how many hours instead, as some of my staff might only need 3 hours off in a specific day?

  18. Marko says:

    Hey, thanks for this great holiday planer. Is it possible that there is more rows? I need more rows, and it seem I cant find the way to insert them? Any solution on this matter will be highly appriciated!
    Thanks in advance

  19. phedon says:

    Amazing vary helpful. Any formulas to plus a half day. I want this to plus half day to the overall when we work overtime.

  20. dimsumman says:

    Brilliant holiday planner! much appreciated.

  21. Jenna says:

    Very helpful saves you lots of time

  22. Julia says:

    Your holiday planner is an excellent resource and has saved me a heap of time. I was able to combine your 2016 and 2017 planners in minutes and I will have a very happy colleague on Monday morning – thank you.

  23. Timothy Cox says:

    The formula below should be copied into cell B2 and then dragged to Cells (B2:AL14), with the year entered in A1, “Mon” to “Sun” entered at the top (B1:AL1) and “Jan” to “Dec” entered along the side (A2:A14). Note the days and months must be 3 letters only. Once populated you can insert new lines (e.g. below each month) and will find the formula quite sturdy

    =IF(IFERROR(TEXT(A2+1,"mmm"),"")=$A2,A2+1,IF(AND(TEXT("01 "&$A2&" "&$A$1,"ddd")=B$1,COLUMN(B$1)<9),VALUE(TEXT("01 "&$A2&" "&$A$1,"YYYY-MM-DD"))," "))

  24. Timothy Cox says:

    Addendum: I suggest you format the cells to Custom, type “dd”, and apply conditional formatting to the columns of
    1) for Cells B1:AL14; cell is blank; fill black
    2) for cells B1:AL14; =OR(B$1="Sat",B$1="Sun"); fill grey

  25. Ian king says:


    how do i add and delete another employee. If I want to amend an employee, do i have to delete the employee first and then re-insert the employee again?


  26. hemant says:

    i am a user of your calendar for 3 year and it is very easy to use, you had one column that carry over last year holiday to next year if you can amend or tell me how to add this will appreciate.

  27. Glory Kuyere says:

    Great stuff, thanks man. Been looking for something like this. Will save me lots and serve me lots.

  28. Lee says:

    Thanks a lot for this. saved me lots of time that would have been spent making one of these for the manager at work

  29. Colin Brown says:

    Brilliant planners used them for several years now. I have however just spotted an error with the 2018 one page Excel calendar. November starts twice on Thursday and Saturday meaning that it ends on a Sunday when it should end on a Friday.

Leave a Reply

Do NOT fill this !