Posts Tagged ‘staff planner’

2018 and 2019 staff holiday Excel planner and one page Excel calendar

Tuesday, January 17th, 2017

It is only January 2017, but I’ve already had my first request for a 2018 staff holiday calendars (I know some people have holiday years that overlap calendar years). So here are the staff holiday planning spreadsheets for both 2018 and 2019. Also I’ve uploaded the 2018/2019 one page excel calendars.

Staff holiday planner

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

2018 2019 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). If you want it to be fixed to the calendar year then you’ll have to tweak the formulas.

See the old 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. You can download them from here:

Download 2018 staff holiday planner spreadsheet – 12kb
Download 2019 staff holiday planner spreadsheet – 12kb

You’ll need to use a relatively modern spreadsheet package such as Excel 2007 or LibreOffice (which is free) to view the whole calendar. This is because the staff holiday planner has over 365 columns and some old spreadsheet package such as Excel 97 don’t support that many columns. 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

In these Excel spreadsheets you’ll get the whole year on a single page. You should be able to print it onto a single sheet of A4 paper if you wanted to.

2018 2019 one page excel calendar

You can load the XLS using any version of Microsoft Excel from 97 onwards, or using the free OpenOffice or LibreOffice.

2018 one page Excel calendar – 4kb
2019 one page Excel calendar – 4kb

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.

2014 and 2015 Excel staff holiday planner and one page Excel calendar

Monday, December 2nd, 2013

I’ve been getting loads of requests for the 2014 versions of my staff holiday planning spreadsheet, and the one page Excel calendar. I thought it was about time I created them. And for good measure I did the 2015 version as well.

Staff holiday planner

First up here are my latest free spreadsheet that can help you track staff holidays, training, sick days and offsite days.

2014 excel staff holiday planner

They come configured 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.
It is easy to customise the formulas if you want to factor in sick days for example. See the 2012 planner for more example formulas. Note that the 2014/2015 calendar formulas count the entire row (which includes the last bit of the year before and the first bit of the year after), whereas the 2012 calendar formulas just count the 2012 year. You can adjust the formulas to suit your requirements.

As always 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 2014 staff holiday planner spreadsheet – 21kb
Download 2015 staff holiday planner spreadsheet – 21kb

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 OpenOffice (which is free). One workaround if you are forced to use Excel 97 at work is for you to use OpenOffice at home to split the spreadsheet into 2, one part for H1, and one for H2.

One page Excel calendar

As always you get the whole year in a very simple one page Excel spreadsheet.

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

2014 one page Excel calendar – 13kb
2015 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.

The 2016 versions of these spreadsheets are here: https://www.reviewmylife.co.uk/blog/2015/08/30/2016-and-2017-staff-holiday-excel-planner-and-one-page-excel-calendar/.

2013 Staff holiday planner spreadsheet

Friday, November 18th, 2011

At the time of writing it nearing the end of 2011 so some of you may be searching for my previous staff holiday planner for 2012. If you are planning very far ahead (or if you are reading this in 2012/2013) you might want my 2013 staff planner. This is my latest free spreadsheet that can help you track staff holidays, training, sick days and offsite days.

2013 staff holiday planner spreadsheet

It comes configured 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.

It is easy to customise the formulas if you want to factor in sick days for example. See the 2012 planner for more example formulas. Note that the 2013 calendar formulas count the entire row (which includes the last bit of 2012 and the first bit of 2014), whereas the 2012 calendar formulas just count the 2012 year. You can adjust the formulas to suit your requirements.

As always 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 2013 staff holiday planner spreadsheet – 23kb

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

2012 staff holiday planning spreadsheet

Monday, April 11th, 2011

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.

2012 staff holiday planner xls

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.

2011 staff holiday planner

Friday, November 12th, 2010

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.

2011 staff holiday planner example

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.

2011 staff holiday planner categories

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.

2011 staff holiday planner blank

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.

2011 staff holiday planner formulas

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!