First let me tell you that my trip to Maldives has been very successful. I had fun teaching excel, playing in the beach and relaxing. Both Jo and my son also loved the place (we left our daughter in India with my in-laws 🙁 ). We came back on Sunday by noon, Indian time and have slept most of the time since. I am planning to write a detailed travelogue and share my experience of running an Excel workshop. But that will happen only Friday.
Since I have too much backlog work, I am going to use this week for some of the guest posts that are pending for a while.
Excel Holiday Request Form
Theodor, one of our readers, first emailed me in December asking a question. But he also made a promise to share some of his techniques with us thru Guest posts. Naturally, I was too happy and invited him to share a file or two so that I can use them for articles here.
Later during Christmas holidays, he sent me this beautiful Holiday Request Form made using Excel.
What does Holiday Request Form do?
Using this form, employees can request for a holiday (leave / vacation). In Theodor’s words,
As promised, here’s a sample of something just finished – a holiday request form.
Nothing too fancy about it except one thing: because we’ve had repeated hassle with people messing up the date format when entering the “from…. until” dates and as a consequence the =NETWORKDAYS() formula would not work, I locked the cells and allowed them to change the dates via scroll-bars.
And then just to ramp it up a notch, the “calendar” at the bottom would be filled in with an “x” for each day requested. Now that’s a formula that bugged me for a few hours, since it has to do with a number of conditions that are apparently conflicting.
How does it work?
Theodor used various techniques and ideas of Excel that we frequently talk about in Chandoo.org. Some of the most important pieces of the puzzle are,
- Scroll-bar form control is used to select dates
- Conditional formatting is used to show x marks for dates on vacation
- NETWORKDAYS() formula is used to calculate vacation duration (while excluding weekends and national holidays) [more on this formula]
I tried to take some screen-shots of his file, but since the layout is big, the images would not come right. So I made a video (5 min) demonstrating the file. Please watch it to understand how this holiday request form works.
Download Holiday Request Form:
Click here to download the excel workbook. The file is protected with blank password. Unlock it to inspect the formulas and formatting rules.
Thank you Theodor
Thanks to Theodor for teaching me how to link things like conditional formatting, form controls and formulas to create simple yet awesome templates for keeping track of holiday requests etc.
If you like the template, please say thanks to Theodor.
Also, please share ideas and tips on how you would enhance this file.
18 Responses to “Holiday Request Form in Excel [Awesome Ways our Readers are using Excel]”
Thanks Theodor, but can u tell me how do i modify the file as per my req
Wow, what an awesome start. Thanks Theodor for this inspiring form. I will try to adapt it to brazilian holidays and so on, only to test my skills. Keep the sheets flowing, man. 🙂
A lot of organizations use forms in Outlook to make vacation requests.
If you do it in Excel, better to integrate it with Outlook's calendar, or at least add some email functionality to email the workbook to your boss.
Wow, probably one of the best spreados I have ever seen, the scroll bars linking to the calendar is particularly impressive. Great work Theodor and thanks for sharing!
Hi Chandoo,
how come the video has no audio ? Or is it just me ?
@Francis, The Video has audio
You'll need to check your system/volume
Hi Chandoo,
This is an awesome file but can we get this file in English language and also can we modify this
Ankur
Hi Ankur.. you can easily modify the file. Just right click on sheet name and select unprotect. The formulas are written in English only. The language is Romanian. You can translate it online.
Hey Chandoo,
Nice to see you back in action. Waiting to read your travelogue 🙂
Hello all and thank you for the uplifting comments!
The main reason behind creating this form is that as per Romanian law, there has to be a written document archived behind every holiday of an employee. So while linking it with Outlook is a good idea, we currently only use the printed form for administrative purposes.
@Vikram - unlock the sheet and as far as I can tell, inputting different legal holidays in AS28:AS38 should do the trick. You may want to play with the grey locked cells in D28:AH39 and drag the formulas if you want to have an "x" appearing anywhere. Here we do not issue holidays in week-ends, as they are days off by default.
Actually, the formulas in D28:AH39 gave me the biggest headache, as they have to comply with apparent conflicting conditions when a holiday starts at the end of one month and end at the beginning of the next.
Will be back with more material, that's for sure! Thanks again for the warm welcome :o)
awesome job...
[...] week we saw a really cool holiday request form made by [...]
Chandoo
Your a star!
Thanks very much-
its because of people like you that my life is made ALOT easier...
Kind regards
Neil
Thanks very much Chandoo and Theodor .
[…] Holiday Request Form template […]
When you change the year, do have to manually change the highlighted weekends?
Hi Debbie,
One has to unlock the sheet, then manually change the week-ends indeed. The reason is of course that one cannot accommodate legal holidays in all countries across the globe in one single template so there is really no point in over-engineering.
Simply copy-paste the formula from the white cells across all months, then delete in from week-ends and other bank holidays. Marking those in gray makes the template easier to follow in my view.
Cheers,
Ted
Hi Theodor,
What if I want to add more rows with dates (for example, I want vacation from 14.08 till 17.08 and on 23rd till 26th)? I tried to extend the formula (which marks the dates with X) but couldn't do it.
Thanks!
Klementina