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.
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.
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
- Excel leave schedule
- Quickly make a schedule (leave) in Excel
- Vacation planning in excel [solved]> Forum - Excel
- Carry out a production schedule> Forum - Excel
- Put a carpooling system on excel> Forum - Excel
- Reservation planning, Excel guest house. > Forum - Excel
- Concatenate Excel: how to merge cells
- Count or sum colorized Excel cells
- Excel chart: create a line chart
- IF, AND, OR, NOT: use Excel's logical operators
- Printing a large Excel table: the step-by-step solution
- Move an Excel column: the very simple solution
- Freeze Excel panes: fix a row or a column
- Sum Excel: all the solutions to make additions
- Remove duplicate Excel files: all techniques
- Excel conditional formatting: all techniques
- Merge Excel Files: How to Join Tables
- Sort alphabetically in Excel… or otherwise!
- Excel formulas: use the basic functions
- Convert Excel to PDF: Convert Table or Graph
- Excel pivot table: example, create, update ...
- Lock Excel cell and protect formulas
- Numbers in Excel: display a zero in front of a number
- Excel functions in English: translations in French
- Excel drop-down list: create, insert, modify, delete
- Go to row in an Excel cell
- Print a table on a single page with Excel