Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Plan and schedule employees to work on a specific day of the week

How to make yearly schedule for certain employees who need to work for a specific company on a certain day

Plan and schedule employees to work on a specific date or day of the year
In this example tutorial I will show how we can plan and create yearly schedule of workers who need to work on a specific date or day for one company. The goal is for a specific day in the course of the week doing the same worker for a certain company. I will try to explain that I have all the functions used in the formula and why the formula is created in that way.

In the picture below you see completed plan and schedule employees for work at various companies.

How to create a plan and draw up a schedule of workers on a certain day for a specific company

For this tutorial in the completion of the formula I have used the following nested Excel functions:
IF, AND, VLOOKUP, WEEKDAY, COLUMN. I'll try to beginners layman terms to explain the functioning final formula.

In the beginning, let's define the basic elements of the problem or task and ask yourself some questions.


Questions:
  • What is the goal of the task?
  • How to organize data?
  • What are the conditions?
  • What result should be returned to the formula?
  • What function could be useful?

Reply:
  • If we take for example, that we are working in the administration of technical support in the company or enterprise engaged in cleaning of certain facilities for certain businesses. So we rent workers for cleaning and we want to plan all workers to work in a specific company, institution, etc. for the whole year. The goal is to create a single formula that can be copied and get the desired results.
  • Here we need to think carefully, because poor data organization, can create problems in any step of the calculation.
  • The conditions, in this case, are the dates of the year that is the name given to a specific date
  • Name of the company, for which we plan to workers
  • Name workers
  • The formula should return the name and surname of the employee to be a regular day to work in a specific company or institution
  • Reflecting on the conditions and functions we need to somehow to think or come up with the idea that the function could be used. Given that we are talking about searching some tables and certain conditions we will be based on Excel search function and Excel logical functions.

Data organization and arrangement of elements in the table

The organization of data we created according to the image above. In the column 'A' we have set all the dates for the whole year and the way that we enter the first start date and then copied down to the last day. In the first title row (Header) we entered the names of firms that plan to workers. Of course in the cell range 'B2:??' we need to create a formula.

The data in column 'A' are formatted with "Custom user format": ddd. d.m.yyyy (ddd. [$-409]mm.d.yyyy;@) to display the abbreviated name of the 'Day' along with the date.

Custom Format Cells for Date in Excel

In the first step, given that it is a search with the terms we have come to the conclusion that the search of the basic elements we use a named database (because the named cells or ranges of data easier to work with). In the picture above you will see that I am a base from which we draw specific data created on the same worksheet. But it does not have to, 'base' may be on another worksheet.


In layman's words, explaining what I was doing.
  1. I created the base and has named as the 'base' because our numbers for each day unique and specific to the name of each day, this we know the function Weekday()
  2. We need to know which number corresponds to which day (Sun=1, Mon=2, Tue=3, Wed=4, Thu=5, Fri=6, Sat=7). We use the absolute address for the column because he is fixed and when copying the formula to the right will not be changed
  3. For a given Day from 'A2' we need to find out which company it belongs to use the function VLOOKUP() to give us back the result in column 3 named base.
  4. For a given day from 'A2' we need to know which name belongs to a certain day, so we use the function VLOOKUP() to give us back the result in column 4 of the base sequence
  5. Function Column() returns a number of columns. In the VLOOKUP formulas in the syntax we use it as an argument for a column, because the copy to the right. Function Column(A1) returns the column number 1, Column(B1) returns the result of 2 and so on.
  6. AND() function we use it in the syntax we can set two conditions and "both" are mandatory. If they coincide, ie, if both conditions are met, the function should return TRUE
  7. IF() function returns the result to us, depending on the conditions. If the condition is fulfilled returns the result of the formula Vlookup2 and if not return a blank cell

Elements of the database (number of calls the day, name day, company name, the name of employee)

Why is it good to create "named the base" of basic data? Because you do not have to write the absolute address range of cells and formulas to look nicer-looking and easier to remember certain parts that are used for the arguments used Excel functions. In the picture above you will see that I used for the database name days a week, for every day of the week I ordered a certain number, company name and the names of the workers. The entire range of cells 'J2:M8' I named "base". In principle, it is desirable to use a dynamic database table because of possible extensions and adding the names of workers and companies.

A specific number of days in the name of a specific date is obtained by the formula =WEEKDAY(A2) in column 'J' I wrote the numbers.

The result of which should be returned to the formula is the name of the workers in the cell corresponding to the intersection of the conditions in column 'A' and the first row with the name of the company in the same column where it needs to be the name of the workers.

What Excel functions used?

Here I want to mention that this is one way to solve the task, because there are ways to solve using other Excel functions. This is my idea of how to solve the problem. So I used Excel functions to search the database and logic functions that work with conditions.
  • First Excel function that is important in this case is an Excel function WEEKDAY(). This function returns to us as a result of number code for a specific date that a specific name on. =WEEKDAY($A2) => returns as a result the number '5'
  • Very popular Excel functions to search the VLOOKUP. The function VLOOKUP() is used to return the two items that will later be a requirement in another Excel function.
    =VLOOKUP(WEEKDAY($A2);base;3;FALSE) => returns a 'Company5'
    =VLOOKUP(WEEKDAY($A2);base;4;FALSE => returns as a result of the 'Jeronim' name
  • Excel functions COLUMN(), which I used to determine the number of columns in the second VLOOKUP() function returns the number of columns when the formula is copied to the right. =COLUMN(A1) => returns a number 1
  • Excel function AND() has more conditions and as a result, returns TRUE or FALSE

To you I better explain the functioning of the formulas used for this event or task, I parse in detail the parts of all Excel functions that are used for the final formula

In the text below you will see the names that I used to explain this tutorial

NumberofDay = ConditionWeekday =>
=WEEKDAY($A2)


CriteriaCompany =>
=VLOOKUP(WEEKDAY($A2);base;3;FALSE)

CriteriaName =>
=VLOOKUP(WEEKDAY($A2);base;4;FALSE)

ColumnNo =>
=COLUMN(A1)


CriteriaCompany + CriteriaName = CriteriaAND =>
=AND(B$1=VLOOKUP(WEEKDAY($A2);base;3;FALSE);WEEKDAY($A2)=COLUMN(A1))


formula =>
=IF(AND(B$1=VLOOKUP(WEEKDAY($A2);base;3;FALSE);
WEEKDAY($A2)=COLUMN(A1));VLOOKUP(WEEKDAY($A2);base;4;FALSE);"")
(Note, if the formula in this tutorial you copy to your computer be sure to plug it into one row or one line).

Lay explained Excel functions in formulas

formula =>
=IF(AND(B$1=VLOOKUP(WEEKDAY($A2);base;3;FALSE);WEEKDAY($A2)=COLUMN(A1));VLOOKUP(WEEKDAY($A2);base;4;FALSE);"")

NumberOfDay = ConditionWeekday =>
=WEEKDAY($A2)

CriteriaCompany =>
=VLOOKUP(NumberOfDay_Condition;base;3;argument)

CriteriaName =>
=VLOOKUP(NumberOfDay_Condition;base;4;argument)

ColumnNo =>
=COLUMN(A1)

CriteriaCompany + CriteriaName = CriteriaAND =>
=AND(B$1=VLOOKUP(ConditionWeekday;base;3;FALSE);WEEKDAY($A2)=COLUMN(A1))

formula =>
=IF(AND(B$1=VLOOKUP(ConditionWeekday;base;3;FALSE);WEEKDAY($A2)=COLUMN(A1));VLOOKUP(ConditionWeekday;base;4;FALSE);"")

=IF(AND(B$1=CriteriaCompany;ConditionWeekday=COLUMN(A1));CriteriaName;"")

=IF(CriteriaAND;CriteriaName;"") => If the result CriteriaAND=TRUE return Name of worker and if not return a blank cell

In the excel cell 'B2' entered the final IF formula, that has been copied down in the right side of the formula is as follows: (important! If you copy a formula with this tutorial on your computer be sure to plug it into one row or line).

=IF(AND(B$1=VLOOKUP(WEEKDAY($A2);base;3;FALSE);WEEKDAY($A2)=COLUMN(A1));VLOOKUP(WEEKDAY($A2);base;4;FALSE);"")

Alternatives to VLOOKUP() function as Index() and Match() function

Instead VLOOKUP formula, which is located inside the IF function/formula as an argument, we can set a combination: MATCH() function that nest in the INDEX() function. You notice that in the IF formula have two different arguments VLOOKUP formula.

So, in the formula above, this argument IF() function
VLOOKUP(WEEKDAY($A2);base;3;FALSE)
can be replaced with a formula INDEX($L$2:$L$8;MATCH(WEEKDAY($A2);$J$2:$J$8;0)

and this second argument IF formula
VLOOKUP(WEEKDAY($A2);base;4;FALSE)
we can replace this formula INDEX($M$2:$M$8;MATCH(WEEKDAY($A2);$J$2:$J$8;0)

So to the final IF formula look like as follows:

=IF(AND(B$1=INDEX($L$2:$L$8;MATCH(WEEKDAY($A2);$J$2:$J$8;0));WEEKDAY($A2)=COLUMN(A1));INDEX($M$2:$M$8;MATCH(WEEKDAY($A2);$J$2:$J$8;0));"")

or if you naming database columns then the following formula returns the desired result

=IF(AND(B$1=INDEX(columnCompany;MATCH(WEEKDAY($A2);columnCondition;0));WEEKDAY($A2)=COLUMN(A1));INDEX(columnName;MATCH(WEEKDAY($A2);columnCondition;0));"")

(Note, if the formula in this tutorial you copy to your computer be sure to plug it into one row or one line).
The final set formula in cell 'B2' and copy down, the next step is that you copy the entire column with formulas to the right