➤Excel planning: how to make a vacation schedule 🕹

Do you need to make a schedule? Here is how to create one very simply with Excel. For the example, you will establish a vacation schedule for you and your colleagues.

If you have to regularly carry out schedules of all kinds, your first instinct should be to issue the command File> New in Excel, then type planning in the search box. You will thus benefit from dozens of very professional looking models in French offered by Microsoft. Time schedule, school or university calendar, weekly or half-yearly planning, employee planning ... the situations envisaged are numerous, both in a professional, school or personal context.

Excel planning: how to make a vacation schedule

All these models are of course adaptable to conform to your requirements. But if you want to make progress in Excel, you can also start with a blank spreadsheet and make your own schedule! This is what we suggest you do step by step in this practical sheet. The example dates from 2009, but you just have to type in the right dates to obtain a schedule for the current year. And of course, this example works with all versions of Excel, even those older than ten years.

How to make a basic schedule with Excel?

In the spreadsheet, start by defining the structure of your calendar. You can then fine-tune the formatting.

A1: Enter "Holidays 2009" (or the current year)
A2: Enter "LAST NAME First name"
A3 to Ax: Enter the name of the x people to manage
B1: Enter 01/05/09 (or for example 01/05/22 for May 1, 2022); Format / Cell / Number / Custom / Type ddd
B2: Enter = B1; Format / Cell / Number / Custom / Type dd mmm
Increment B1: B2 on the right over 184 columns (until 31/10); to do this, select B1 and B2, pull the handle (bottom right), then in the incremented copy options icon choose Increment a series
Select the complete sheet and activate the Conditional formatting with Cell value = "c" and Format dark blue font + light blue fill pattern
If necessary, enter this legend at the end of the table: c = day off (displayed in blue)
The basic work is done. Enter for each staff the letter "c" in the boxes corresponding to their days off: they appear in blue!
The Excel document as it should be after these steps: https://www.cjoint.com/c/DEowHH2ikpo

How to customize the presentation of a planning with Excel?

Now that the structure is in place, polish the presentation as you wish. You could for example bet on a dotted grid to avoid the very simple grid, kind of crossword puzzle.

Lines 1 and 2 in size 8 font; line 2 in orient ° vertic.
Reduce the column width (approximately 4,00 or 33 pixels)
Add in the cells a validation rule with message (instead of a fixed legend at the bottom of the table)
Add an MFC condition for Saturdays and Sundays.

Manage working days

In addition, it is possible to report working days (since the version of Excel 2007). This option is even recommended!
If you want to use this schedule to count the number of working days / working days consumed by each agent, it is necessary to add a marking of Saturdays, Sundays, public holidays and other bridges. Here is the procedure:

Insert a row 3.
Enter the formula = IF (WEEKDAY (1: 1; 2)> 5; "f"; "") down the line.
type F for each other non-working day (bridge, party).
In GD4, enter the formula = NB.SI.ENS (B4: GC4; "c"; B $ 3: GC $ 3; "") and copy down.
Document obtained: https://www.cjoint.com/c/DEowKo4UVW1

Ensure automated filling

Now let's add columns to specify the start and end dates of a person's leave ...

After column A, insert 3 new columns: Start, End, Working days.
Columns B and C must be put in date format jj / mm ; enter the vacation dates for each agent.
Give column B the name beginning, in column C the name end, on line 2 the name data and in line 3 the name F.
In cell E4 (first box of the planning grid) enter the formula = IF (AND (date> = start; date <= end; NOT (F = "F")); "C"; "") to be copied in all the boxes. It is this formula which will enter C in the boxes concerning the non-working days of the holiday period.
In cell D4, enter the formula = NB.SI (E4: GF4; "C"), to copy down. The GG column having become useless, delete it.
To enhance the presentation of the planning, from E4 apply a Conditional formatting with The formula is =E$3="F" and a color fill, then extend this MFC to all the other boxes of the planning, on the right as at the bottom.
Example file: the final result in 2007 format can be downloaded here: https://www.cjoint.com/c/DEowMzruvxu

How to add data to the planning?

Do you want to add employees to your schedule? Or create a different schedule for another department in your company? Follow our advice.

Add people to the schedule

If four lines need to be added, for four new agents, select for example line 6, press Copier, select lines 2 to 5 and do Insert copied cells.
We get 4 "clones"of line 6, and all that remains is to modify the data; the formats and formulas are already available ...
It is important not to add these lines after the existing lines.

Add a sheet to create another schedule

If you need to add another sheet to the file (for another period or another group of people), proceed as follows ...

Right click on the sheet tab
Select Move or copy / Create a copy / OK
To change people, modify column A.
To change the period, change the dates to E1 and F1, select these two cells and increment by dragging the handle to the right.
Rename the sheet
Range names are kept, but each assigned to the relevant sheet.

Edit autofill

We offer you another way to ensure automatic filling below. To obtain the automated filling of cells in violet for a period of sick leave, you have to repeat step 6:

By making sure that the conditional formatting for the filling of cells contains the violet rule for the letter code "M".
By inserting 3 other columns start, end, JO.
By setting the names [startm] for the range E2: E11 and [endm] for the range F2: F11.
By completing the formula in H4: GI11 = IF (AND (date> = start; date <= end; NOT (F = "F")); "C"; IF (AND (date> = startm; date <= end; NO (F = "F" )); "M"; ""))
Document obtained: https://www.cjoint.com/c/GDznRW60kvy

Thanks to Raymond Pentier who originally produced this practical sheet.

Excel planning: how to make a vacation schedule

If you have to regularly carry out schedules of all kinds, your first instinct should be to launch the File> New command in Excel, then to type schedule in the search field. You will thus benefit from dozens of ...

I manage my push subscriptions