When I was managing a big team, one of the many challenges was the holiday calendar or I simply called the team calendar. To have everyone happy and to be sure to not impact the service, I needed to organize it very well to reflect the full team and the support workgroup.

The typical things to control are the holiday, the bank holiday, the sickness, the departure, the arrival and the law day (for instance, voting day, demonstration day, maternity, etc.). Now there can be some other inputs like:

• Local bank holiday, particularly if you provide support for different countries
• Shift, if you have different teams doing a 8h and/or 12h shift to cover a 24h support
• Extra hours, if a member does extra time and she/he wants to take it as holiday

There are many tools in the market but the cheaper one, it is to use excel and a little of my time to design the perfect calendar. Specially, if you are working in a company that doesn’t want to spend money for this kind of thing.

I use 1 excel per year and I create 13 sheets, one for each month and one extra for follow up so I just need the data from HR once a year or each time there is something wrong detected. In my excel, I am organizing this way:

The column is representing the day of the month

The row, I have at the top, the country then the workgroup name then the members

Except for the 13th sheet, the column is representing the month and the row, the members.

For instance, a 2016 team calendar:

To convert the time into decimal number, the formula on AH2 is just:

=AG2*24

And I format the cell in “number”. For the time on AG2, the format is “custom” by putting “[h]:mm:ss”.

If a member is giving support to different countries, I will put him/her to all those countries. It is quite unusual for the levels but it may happen that the member is giving also support to different levels, particularly as a backup agent, in this case, I do the same thing. For instance, in one of the companies, I managed 1st and 2nd level, one of the 1st level had knowledge and experience to do upper level support when a 2nd level guy was absent.

I would like to say that it is very useful because it gives a better margin to manage the calendar, much more if the person is willing to do it for helping instead for extra money, particularly true if your company is not willing to pay but you can find some agreements. For instance, giving her/him extra hours. And if your company is not supporting you about this kind of action, do it unofficially if it is possible.

Using 1 column = 1 day (except for the follow up sheet) makes the check easier because I can know which day exactly the person was absent. The other reason is that in one single look, I can see if the service will be impacted negatively or not so I can approve or not the holiday.

So after explaining the base of my structure, I will tell you about the content. I use different colors to represent the type of absence because it is visual more speaking (for instance, green for holiday, grey for bank holiday, etc.) and I put a letter (for instance, H for holiday, BH for bank holiday, etc.) except for the extra time, I will put a decimal number corresponding to the time (for instance, 5 if the person does 5 hours more and -2.25 if the person takes 2h15).

Do you remember that I told you that I create 13 sheets, well the 13th is containing a formula for each member to calculate everything for a better follow up so I will know how many holidays taken and not, extra hours spend and not, etc. For instance:

For holiday represented by H, the formula on cell E3:

=COUNTIF(January!$B$4:$AF$4,"h")

For extra time, the formula on cell F3:

=SUM(January!$B$4:$AF$4)

For the remaining extra time, the formula on cell P3:

=IF(O3<0, "-" & TEXT(ABS(O3/24),"[h]:mm:ss"), O3/24)

And the format is “custom” by putting “[h]:mm:ss”. Don’t format in “time” or putting “hh:mm:ss”, it will not show more than 24 hours. This formula allows me to show a negative hour if the agent consumes more than he has.

It may happen that the data is not correct, because, normally, you will not be the only one to track those things, the technician will do the same so when someone asks me about how many holidays he has and he will tell me “ahhhh your number is not the same as me”. So I can check with him and update the correct data if it is wrong.

If both of us have some doubt, in this case, I will ask the data from HR and we will review it. In this case, usually, the HR data is expressed in decimal number, for instance, if I ask “can you let me know how many holiday left for this person ?”. The answer may be 1.9 days or 37.94 hours. Since I am not intelligent, I need to use a formula to convert it in a data that everyone can understand better. For instance:

For the time, the formula on cell S3:

=Q3/24

And the format is “custom” by putting “[h]:mm:ss” so the result will be “37:56:24”.

For the day, the formula on cell T3:

=INT(R3)&" days + "&TEXT(R3,"hh:mm")

And the format is “general” so the result will be “1 days + 21:36”.

The painful part is when I create the first time the excel file because I had to put everything and to be cautious to assign the formula to the correct person. Once done, for the next year, it is just an update, particularly for the weekend and bank holidays since I like to highlight them in grey.