Speaker 1: Hello everyone. Today I will show you how to create a work schedule in Microsoft Excel. The lesson will be useful for those who are just starting to master this program. But experienced users will also be able to learn something new for themselves. If this video seems too fast or slow to you, then you can easily choose the playback speed that suits you. And if you want to watch videos without sound, then just turn on subtitles. I wish you a pleasant viewing. So let's get started. I click on one of the cells and type the number 1 I press enter. Then click on this cell again and move to the main tab to the section editing. I open the fill options. And I choose the point called series. Since I am making a work schedule for the month of September, then in the line stop value I specify the number 30. After all, this month is exactly the number of days. I click OK. This way I performed autocomplete with the specified condition and I didn't have to enter these numbers manually. On the other hand, I could do it by stretching. I'll demonstrate later. Let's continue. I want to align the width of the columns. To do this, I click on the top left corner of a worksheet. Then, I put my mouse cursor on any border between the columns. I hold down the left mouse button and while holding it down, I align the widths of the columns. I think this option suits me. Next, the first of September falls on Friday, so I abbreviate this day of the week in the row below. I click on this cell, move my mouse cursor to the bottom right corner, and while holding the left button, I extend the autofill till the 30th date. A large part of the data in my table will be centered in the cell, so I select the area I need and click on the corresponding icon. The next step is to fill in the information about the employees. I select two cells and with this command, I merge them into one large cell. I use the autofill to merge the cells below. Now I can see the data in the cell. I can also use the autofill to merge the cells below. Now I will write the column title and the names of the employees. Done. The next step is to add borders. I select my table and click on this icon. In the drop-down list, select the item All Borders. And now it will be the most interesting. I need to enter the working hours of each employee as well as his days off. In addition, employees periodically go on vacation and sometimes go on sick leave. All this should be reflected in the work schedule. So, the first employee comes to work at 10, 0 am. I enter this information into a cell and after entering it, its format automatically changes. I will slightly reduce the font so that the graph does not take up too much space on the sheet. I also have to specify what time the working day ends. You can record the time manually or you can use the formula. In my case, all employees work for 8 hours plus another hour is allocated for lunch. So his working day will end in exactly 9 hours. I put the equal sign. I click on the cell with the specified arrival time, put a plus sign and write the word time. The program itself selects the desired function even before I have finished typing. I click on it and indicate the following values in parentheses where 9 is hours and 2 zeros are minutes and seconds. So I add exactly 9 hours. Please note that I put a semicolon between the arguments, but in your version of Excel, you may need to specify a comma, otherwise the formula will not work. The fact is that this program has its own peculiarities in different countries, so try putting a comma between the values, and if this does not work, then specify a semicolon. As you can see, the program automatically added 9 hours and filled in the time of leaving work. For the next 4 days, this employee will have the same time of arrival and departure from work, which means I can just copy these two cells. Watch how I do it. Next, the employee has 2 days off. I'll just combine two cells into one and instead of day off I'll write abbreviated DO. Then I'll copy it the next day. And so I fill out the entire schedule. So, instead of manual input, I mostly use copying identical cells and this saves me a lot of time. For example, now I just selected a range of cells, pressed CTRL plus C to copy and CTRL plus V to paste. Since the 19th day, this employee goes on vacation and I, by analogy with the weekend, fill in the remaining cells, for reduction I will write VAC. Now I will fill out the work schedule for the rest of the employees. I will fast forward this procedure so as not to waste your time. Done. It's time to take care of the appearance of the table so that it would be easier for employees to navigate it. Watch how I do it. So, I have just added the table name and visually separated the top two rows using a fill and also made hints in the form of color symbols. Now I'm going to show you a trick. I select the table and in the style section click on the icon conditional formatting. In the drop-down list, select the item, new rule. In this window, I select format only cells that contain. And here I put specific text. A little to the right, I type do and click on the format button. In the fill tab, I select the green color and click ok. Now all weekends are highlighted in green. Even if I want to add a day off to an employee, it will automatically turn green in the schedule. I think it's a convenient thing. As for vacation and sick leave, it will be faster to perform the usual filling there. Done. It remains only to calculate how many employees work per day. This can be done manually. But this procedure will take a lot of time. After all, Excel is a program for the lazy. So I'll use the formula and autocomplete. I put the equal sign and write count. Then I select a range of cells and close the bracket. After that, I add a division sign and print the number two. After that, I add a division sign and print the number two. Thus, the program counted the number of selected cells, with the exception of the cell where there is a text format. And the final result had to be divided into two, because each employee has two cells. And if I hadn't done this, the result would have been not five, but ten. That would be wrong. Now I will do the autocomplete. How about calculating the total number of working hours of each employee? If we consider that the working day for all employees is eight hours, then this can be done very easily. I add another column to my table, write the name and make the appropriate design. So, next, I write almost the same formula as for calculating the number of employees. Only at the end, I indicate the multiplication sign and write the number eight. So, friends, now you can create your own work schedule in Excel. I hope you liked my version. Please like if the video was useful and subscribe to my channel. I wish you success in your studies and thank you for your attention. Goodbye.