Staff / Team Holiday Planner Spreadsheet (Excel) Template

Software
1

My employer has introduced an ‘improved system’ to manage staff holidays and absences etc; it is not fit for purpose as it does not provide us all with a ‘team view’ - we can only see our own individual planned absences.

This causes problems such as:-

A) inability to look at the upcoming week(s) and get a feel for who is in and out of the office and manage work/resources appropriately.

B) when looking to book absences throughout the year we are doing it blindly with no view of who else is already planned to be off (they get authorised by the office manager who does have a team view). This means that lots of requests get rejected because of resourcing issues/clashes and this is a lot of wasted time.

We have to use the ‘system’ as HR have visibility over this.

I have looked at many annual calendar and team holiday planner excel templates that are freely available online but none of them have the added functionality that would save us a lot of time.

We have to submit and agree the majority of our annual leave for the upcoming year before the year commences, i.e. we have to submit our holidays requests for 2020 before 31st December this year.

I would like a spreadsheet where all these dates could be input (for 15 staff and 30 days each) and then for it throw out clashes under certain ‘rules’. These rules are quite bespoke in that say Staff Person No 1 for example cant be off the same time as Staff Person No 2, and we can only have 1 out of Type A (of which there are 3 off) off at anyone time, and then we can only have 2 out of Type B (of which there are 5) off at anyone time.

Our current system hasn’t worked as we have had many occasions when we have too many people off, or too many of a particular role off at any one time.

It would need to be a spreadsheet as we can’t introduce any external software or web services into the office. The entry of the staff holidays at the beginning of the year is clearly a very manual task, but i would like to automate the running of rules to flag any occurrences when any of the pre-destined clashes occur.

Is this achievable? How would I go about creating this automation / rule running in a spreadsheet?

Many thanks if you can help and point me in the right direction.

2

Yes, of course achievable … but sounds like you’ll want to use Microsoft Excel with VBA programming to implement your rules. The programming and testing won’t be particularly simple due to the complexity of your rules. Probably would be simpler if you use a different programming language (with Excel as the front-end interface for user) but I understand you can’t install software. Even without using another programming language e.g. Python which is on all Macs as standard, it won’t be for the inexperienced programming to implement, in my opinion. So seek out a programmer and go with their strengths and recommendations.

1 Like
3

I’m assuming you have the capability of using some sort of shared calendar? That would seem much easier than using a spreadsheet.

If you’re only doing it for yourself, you could do the same thing with a calendar.

All else fails:

https://www.amazon.com/Large-Dry-Erase-Wall-Calendar/dp/B01HUC7GV6

1 Like
4

We can access other team members calendars via (cough) IBM Notes; that assumes that the individuals have put all their annual leave into their own personal calendar at the start of the year. This just doesn’t work unfortunately.

We’ve also got a large calendar up on the wall, which is great on1st January but again this tends not to be updated when things change and then all of sudden it’s not a trusted system and not reflection of the truth.

2 Likes