Posts Tagged ‘staff’

2022 staff holiday calendar – free excel spreadsheet

Sunday, October 31st, 2021

It is getting close to 2022 so your staff might be starting to request holiday and time off for 2022. Following on from my 2021 staff holiday planner, is my free spreadsheet for 2022.

2022 staff holiday planner excel spreadsheet

It can be very useful to visualise everyone’s holiday/vacation on a single spreadsheet as then you can make sure that time offs between staff are appropriately distributed. Equally important is to ensure that everyone takes their full holiday allowance!

This spreadsheet can also be used for tracking training, unpaid leave, working from home and sick days. You could add additional formulas to track these other types of leave if they are important to you.

As usual the spreadsheet has the days of the year running from left to right, with a separate row for each member of staff. There are some in built formulas for calculating remaining holiday. You’ll see the formula works by counting cells with either ‘Holiday’ or ‘Half’ in the text, but you could easily customise these formulas to fit your purposes. E.g. you might prefer ‘vacation’ if you are US based.

The holiday tracking spreadsheet could either be used as a locally stored standalone spreadsheet, or if you uploaded it to Office 365 you’d be able to share it with other members of staff. If you aren’t using Microsoft products you can edit this staff holiday planning spreadsheet using the free Libre Office.

Week numbers are included, based on the standard ISO 8601 method of recording the weeks.

Download 2022 staff holiday planner spreadsheet – 13kb

If you have suggestions for improvements to my staff planner please feel free to leave a comment. Hope this spreadsheet makes your 2022 easier to manage.

2021 staff holiday Excel planner – free download

Tuesday, July 13th, 2021

Here is my free Excel staff holiday planner for 2021. There are lots of paid ways of tracking staff holiday, but if you are a small business or organisation then perhaps you want something simple and free.

2021 staff holiday planner excel spreadsheet

This free spreadsheet allows you to customise the people listed and then record holiday, training, and other absences easily. There is a built in formula to track how many holidays each member of staff has left. The easiest way to use the spreadsheet is to copy/paste the categories in from the category box on the top left. If you paste in a ‘Holiday’ then the remaining days goes down by 1, and if you paste in a ‘Half’ then the remaining days goes down by 0.5.

The category box and the employee names on the left are in a frozen pane, so they are always on screen.

Week numbers are included, based on the standard ISO 8601 method of recording the weeks.

Download 2021 staff holiday planner spreadsheet – 13kb

You can load this 2021 calendar into either Microsoft Excel or the free Libre Office. Note that if you are using Microsoft Excel you’ll need to use at least Excel 2007, as older versions like Excel 97 don’t support enough columns to fit in the full 365 days of the year.

The staff holiday planner is easily customisable. You can for example add in public holidays, change the categories, and alter the staff members. Just make sure to double check the ‘remaining days’ formula if you make any changes to the number of columns, you’ll need to make sure it counts the full year to get the correct calculation.

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.

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.