Suppose you want to find out how much time an employee spends on a certain operation on a conveyor belt, or determine the time required to fill an order at an express cafe during peak hours. There are several ways to calculate time differences.
Presentation of the result in standard time format
There are two steps you can take to present the results in a standard time format (hours: minutes: seconds). Use the subtraction operator (-) to find the difference between the time values, and then do one of the following:
Apply a custom code format to a cell by doing the following:
Select a cell.
On the tab home in Group Number click the arrow next to the field General and select Other number formats.
In the dialog box Cell Format select from the list Number formats paragraph (all formats). Then into the field Type select the desired format.
Use the function text to format time: When using time format codes, hours never exceed 24 minutes, never exceed 60, and seconds never exceed 60.
Example of table 1 - Displaying the result in a standard time format
Copy the table below onto a blank sheet of paper and edit as necessary.
|
|
|
Note: If you simultaneously apply a format to a cell using the function TEXT and number format, then the format applied using the function TEXT, will have higher priority.
For more information about using these functions, see the TEXT function and Displaying numbers as dates or times.
Example Table 2 - Presentation of result based on unit once
To perform this task, the function will be used whole or functions hour, minutes And second as shown in the example below.
Copy the following tableto a blank sheet and then modify as necessary.
|
|
|
For more information about using these features, see the following articles:
Instructions
If this is your first time encountering a time calculation task, try not to immediately resort to complex functions. Most calculations can be done using simple mathematical formulas: addition and subtraction. And be sure to format the cells correctly.
To do this, select the range of cells into which data will be entered and right-click on the selection, or open the “Home” tab and click the “Format” button in the “Cells” tool block. In both the first and second cases, select the “Format Cells” item in the context menu.
A new dialog box will open, make sure you are on the Number tab. In the list located on the left side of the window, select the “Time” item with the left mouse button. In the right part of the window, indicate in what form it should be presented. time. Click on the OK button to confirm your choice.
Enter data into the cells in the format XX:XX (or XX:XX:XX). To calculate the time interval between the first and second value, place the cursor in the cell where the total value appears. In the formula bar, put an equal sign, left-click on the cell with the last value, enter a minus sign, then click on the cell with the first value. Press Enter.
For example: the period of time elapsed from 11:10:15 to 12:30:00 is 1 hour, 19 minutes and 45 seconds. The total value in cell C2 is calculated using the formula: =B2-A2. If you need to calculate a period of time in multiple rows (A3 and B3, A4 and B4, and so on), simply drag the AutoFill marker from cell C2 to the end of your table column (C3, C4).
If you need to calculate the total number of hours, minutes and seconds it took to do something, simply add up the data obtained as described above using the SUM function. Place the cursor in the cell with the total value, click on the fx button, in the window that appears, select the SUM function with the left mouse button and mark the range of cells that will participate in the calculation. Press Enter. The formula for calculating will look like this: =SUM(C2:C4).
Microsoft Excel is a powerful tool for working with tables, providing almost limitless possibilities for collecting and analyzing all kinds of data, constructing charts, and creating reports. However, you can also use it as a regular calculator.
- In any, enter =2+2. The result of the calculation will appear in the cell - the number 4. Please note that if you simply write 2+2, then this exact text will be visible. In order for Excel to understand that the user wants to perform operations, and not just write them down, it is necessary to precede the formula with an = sign.
- The rules for performing calculations in Excel are the same as in regular school arithmetic. For example, the results of calculating =(5+5)*2 and =5+5*2 will be completely different. Use parentheses to determine the order of calculations you need.
- The ability to calculate using Excel would not be so useful if the program did not allow you to use the contents as data for calculations. Enter 10 in any number, 2 in any other cell. Enter the = sign in another cell, then left-click on the first cell, enter the - sign and left-click on the second cell. A similar to =I9-G8 will appear, and after pressing Enter you will see the result of the subtraction - the number 8. Try entering other numbers in the first two - you will see that the value in the third changes automatically, immediately after adjusting the data in the first cells.
- Excel has formulas that allow you to process entire arrays of cells. Select several cells arranged in a vertical column and enter any numbers in them. Place the cursor on the cell below the bottom cell with the number. Type =sum(then highlight with the mouse the numbers. Type the closing parenthesis and press Enter. As a result, a number equal to their sum will appear under the column. Excel has many more complex ones that allow you to perform a variety of complex mathematical, statistical and other calculations.
Sources:
- how to calculate formulas in Excel in 2019
Microsoft Excel is very convenient to work with graphs, tables, and databases. A distinctive feature of this Office suite application is that it provides calculations that the user can independently specify using formulas. One of the simplest and most frequently used formulas is the summation of values.
Managers of organizations are required to indicate data on man-hours worked in the static observation form No. P-4 “Information on the number and wages of employees,” approved by Rosstat Order No. 498 dated October 26, 2015. It also approved the rules for filling it out.
Formula for P-4, man-hours, calculation:
CHH = CH1 + CH2 + … + CHN,
Where:
HH - number of man-hours worked;
CHN - the number of hours worked by each specific employee.
They add up all the time worked by the employee on working days in the organization, as well as outside it. , work in , work at (at the same enterprise) are taken into account.
Therefore, if an enterprise has employees who work full-time and part-time, separate calculations are carried out for them using. The formula in this case will look like this:
HH = KR * RV,
Where:
HH - man-hours;
KR - number of employees;
RT - time actually spent on work.
Calculation of man hours
A man-hour is a unit of working time that corresponds to one hour of work by one person. With its help, it is convenient for the employer to plan the working hours of employees, determine the required number of workers to complete the work, and also set deadlines for completing tasks.
H = K * T,
Where
H is the indicator itself, man-hour;
K - the total number of employees of the enterprise;
T - unit of time, hours.
But there are periods that should not be taken into account when calculating. This:
- period of employee illness, according to;
- time ;
- the period is not taken into account;
- the time by which the working day of workers of certain categories was reduced in accordance with the instructions of the legislation of the Russian Federation;
- time given to an employee who has recently given birth to breastfeed her baby;
- other reasons.
Example
Let's give an example for a small company. Let's imagine that it employs 10 workers. The total number of hours worked by them per day is 80 man-hours:
10 people * 8 hours
The resulting number must be multiplied by the number of working days in the month:
80 man/hours * 21 days = 1680 man-hours.
Now let's calculate this indicator for each employee.
With a five-day working week and an eight-hour working day, the calculation will be as follows:
21 days * 8 hours = 168 man-hours
Man-day
We sorted out the man-hours. In this section, we will consider what is commonly understood by the term man-day. This is a unit of measurement of working time corresponding to the working day of one person, regardless of the number of hours worked. It should be noted that this indicator is less accurate than man-hour.
In man-days it is measured:
- days actually worked by the employee;
- turnout time;
- days when the employee did not show up for work;
- downtime, including a whole day or more;
Also taken into account:
- days spent on business trips;
- those days on which the employee received orders from his enterprise to work in another organization;
- days when, due to being at the main workplace, the employee was involved in other activities of the enterprise.
Calculation of man days
Many people are interested in calculating the total number of man-days.
Calculation of man-days, formula:
Kchdn = ∑Kchh / Prab,
Where:
Kchdn - the total number of man-days worked;
∑Kchhour - the total number of man-hours for the reporting month;
Prab - length of the working day.
- first we calculate the total number of man-days;
- Next, we determine the average number of part-time employees in terms of full-time employees.
The formula will be as follows:
S/S number is incomplete. = Kchdn/Krdn,
Where:
S/S number is incomplete. - average number of part-time workers for the reporting month;
Kchdn - total number of man-days worked;
Krdn - the number of working days according to the calendar in the reporting month.
Calculation of man-hours for the year
How to calculate man-hours for a year? To obtain this indicator, it is necessary to add up all the hours worked by the employee on working days in the company and outside it. That is, the calculation includes labor time on work trips, overtime work, as well as work in a combined position in the same company.
B is not taken into account when calculating:
- the period of illness of the employee specified in the sick leave certificate;
- vacation time for nursing mothers;
- the time when the employee did not work for reasons that did not depend on him;
- time of the employee's annual leave;
- reduced working hours;
- the time when the employee improved his qualifications without being involved in the work process;
- time of employees' participation in strikes;
- other reasons for the employee’s absence from work.
The total amount consists of all man-hours for each employee. If you need to calculate man-hours for a year, the calculation using the formula will be as follows.
CHG = CHG1 + CHG2 + … + CHGN,
Where
CHH - the number of man-hours worked during the reporting year;
NHN - the number of hours worked by the nth employee during the reporting year.
Express your opinion about the article or ask the experts a question to get an answer
One of the tasks that a user may encounter while working in Excel is adding time. For example, this question may arise when compiling a working time balance program. The difficulties arise from the fact that time is not measured in the decimal system that is familiar to us, in which Excel works by default. Let's find out how to summarize time in this application.
In order to perform the time summation procedure, first of all, all cells that take part in this operation must have a time format. If this is not the case, then they need to be formatted accordingly. The current cell format can be viewed after selecting them in the tab "Home" in a special formatting field on the ribbon in the toolbox "Number".
Method 1: clock reading after a period of time
First of all, let's look at how to calculate what the clock will read after a certain period of time, expressed in hours, minutes and seconds. In our specific example, we need to find out what time the clock will be in 1 hour, 45 minutes and 51 seconds, if the time is now set to 13:26:06.
Attention! Using this method, you can find out what time the clock will show after a certain amount of time only within one day. In order to be able to “jump” over the daily line and know what time the clock will show at the same time, when formatting cells, be sure to select the format type with an asterisk, as in the image below.
Method 2: Using a function
An alternative to the previous method is to use the function SUM.
Method 3: total time addition
But more often in practice it is not necessary to determine the clock reading after a certain time, but to add up the total amount of time. For example, this is required to determine the total number of hours worked. For these purposes, you can use one of the two previously described methods: simple addition or use of the function SUM. But, in this case, it is much more convenient to use a tool such as autosum.
As you can see, there are two types of time addition in Excel: total time addition and calculating the position of the clock after a certain period. There are several ways to solve each of these problems. The user must decide for himself which option is best for him personally for a particular case.
To understand how to correctly calculate working hours, you need to understand what this concept means. It is disclosed in the Labor Code of the Russian Federation, as well as in the norms of personnel records management. The employee’s salary also depends on the correct calculation.
Time sheet
In Art. 91 of the Labor Code of the Russian Federation states that the normal working week is 40 working hours per week. This means that an employee with a normal work schedule must perform his direct work duties 40 hours a week.
But there are also circumstances in which, according to the norms of the Labor Code of the Russian Federation, the employer must establish:
- 24-hour work week;
- 32-hour work week;
- 36 hour work week.
The Labor Code of the Russian Federation also states that any employer, regardless of what organizational and legal form his company has, must keep records of working hours. If during an inspection it is discovered that there is no time sheet at the enterprise, this will lead to the fact that both the company itself and the official will be held administratively liable.
A time sheet is a form that contains all the information about how many hours each specific employee worked. Absenteeism from work for good or bad reasons is also noted here.
In order to maintain a timesheet correctly, one of the following conditions must be met:
- this can be done daily, in fact noting the presence or absence of workers at their workplaces;
- entering data if there are some deviations. For example, if an employee fails to show up for work without a valid reason.
Based on the document data, at the end of the month, wages are calculated for all employees. It is for this reason that it is so important to maintain this document correctly.
Correct and timely maintenance of timesheets allows you to monitor the process of compliance by each employee with labor discipline, as well as working time standards. In addition, this document also notes hours of overtime work or going to work on a legal day off. Such performance of labor duties is subject to increased payment.
The working time sheet is the main document that labor inspectors request for inspection. In addition to the fact that it can be used to check compliance with legislation regarding working time standards, it is also the main source of statistical reporting on personnel.
Timesheet maintenance is assigned to an individual employee, whose work is controlled by the head of the structural unit. It is the boss who is aware of the presence and absence of employees.
To record working hours, forms T-12 and T-13 can be used. But the law does not oblige the use of precisely such forms. Each enterprise has the right to independently develop a working time sheet form. But it must contain certain information about the company. You can keep timesheets either on a computer or manually.
Calculation of summarized working time recording
This calculation is used in cases where it is not possible to maintain the normal working week. For example, employees work in shifts. With such a work schedule, it is necessary to use not only a different accounting of working hours, but also calculate wages differently.
Under normal working conditions, the working week should not exceed 40 working hours per week. That is, it is 5 days a week for 8 hours. But some employers apply a different work schedule when the daily shift extends beyond the 8-hour workday. As a rule, such a schedule is used in organizations that provide services to citizens or trade. For example, medical institutions, convenience stores, pharmacies, transport, etc.
To comply with the law and increase labor efficiency, the employer introduces shift work schedules. To avoid violations, it is necessary that the duration of work duties during the accounting period is no more than a normal week, multiplied by the number of weeks in the accepted accounting period. The maximum length of the accounting period is generally 1 year, according to Art. 104 Labor Code of the Russian Federation.
Calculation of overtime with summarized recording of working hours
Overtime work is working beyond your regular work schedule. Recently, more and more often, workers have begun to stay at their workplaces beyond the normal 8-hour working time. And if a shift schedule is approved, then overtime occurs constantly.
Every employer knows that overtime work is paid according to slightly different rules. In addition, the duration of such work cannot exceed a certain limit per month. In order to save money on paying overtime hours, employers began to use summarized recording of working hours.
The employer is obliged to calculate overtime when recording working time in aggregate when the following situations arise:
- during the accounting period, an employee who was previously on sick leave, on study, on vacation, or on advanced training courses ended up working more than he missed;
- the employee, due to certain circumstances, worked more during the accounting period than he should have worked during the same period.
For example, the accounting period provides for 1,250 working hours, and the employee worked 1,350 hours during this time. The employer is obliged to pay him for 100 hours at increased rates.
Working time calendar
This document is also called a production calendar. He is every accountant's assistant. This document reflects all working days, as well as weekends and holidays.
This calendar is indispensable for normal work time tracking, that is, with a 40-hour work week (5 days a week, 8 hours each). Using this document, you can correctly calculate the standard working hours during a certain accounting period.
It is not difficult to calculate all the necessary indicators for calculating wages, especially if the employee worked for a full month. Even if you have “sick leave” or “vacation” days, calculating the number of working hours is not difficult.
The working time calendar contains the following information:
- the number of calendar days in each month, each quarter, half-year and per year;
- the number of working days in each month, quarter, half-year and year;
- the number of weekends and holidays in each month, quarter, half-year and year;
- the number of working hours in each month, quarter, half-year and year, with different lengths of the working week (40 hours, 36 hours, 24 hours).
Pre-holiday days are also celebrated here, in which the working hours are reduced by 1 hour.
Calculation of part-time work
According to Art. 93 of the Labor Code of the Russian Federation, part-time mode is a work schedule in which the employee has a part-time job and works either part-time or part-time. This mode of work can be established either at the request (written, that is, statement) of the employee himself, or by agreement of the parties. The employer does not have the right to forcibly transfer an employee to such a work schedule without his written consent.
The employer is not obliged to respond positively to the employee’s request to transfer him to part-time work, but there are circumstances in which he is obliged to do this:
- the employee brought a certificate of pregnancy;
- request of one of the parents/guardian/legal representative having a child under 14 years of age. If the child has a disability, then the age threshold is slightly higher - 18 years;
- the need to care for a close relative if there are medical indications.
In order to correctly record part-time work, it is necessary to start from what is the standard working time for this category of workers. In most cases, this is 40 hours a week - 5 days of 8 hours plus 2 full days off.
In the application, the employee indicates how he is asking for his work schedule to be reduced. For example, he asks to work not 40, but 30 hours a week. Then his daily working time will be 6 hours with a 5-day work week.
Calculation of working time rates
Some citizens can get a “part-time” job. What does it mean? This means that an employment contract with such an employee will be concluded for a part-time or part-time work week. That is, such an employee will not perform his duties the entire working day (not the entire working week), but only part of it.
At its core, this is a part-time calculation. How to produce it has already been described above. The main thing is that the conditions and working hours must be specified in the employment contract. Then there will be no violations of labor laws.
Calculation of working time costs
- identify shortcomings in the organization of labor itself and the production process, which lead to the company losing financial resources and using them inappropriately;
- establish service standards;
- establish standards for the number of hired personnel;
- identify failure to fulfill assigned tasks or, conversely, significant overwork, which also lead to financial losses.
Depending on what factors are to be identified, the following methods of photographing working time are used:
- individual photograph - the object of study is a specific workplace with a specific employee. The more detailed the research is, the more accurate the result will be. It can be applied to the entire structural unit in which the studied workplace is located;
- group photography - research methods are applied to a separate group of workers who are not connected to each other by a single labor and production process.
Procedure for calculating working hours
In order to correctly calculate working hours, it is necessary to take into account many factors. These include:
- type of working week - it can be either a five-day week or a six-day week;
- duration of the day's work shift;
- the start and end period of the work;
- possible and mandatory breaks;
- the order of working days and non-working days;
- how many shifts there are per day;
- the presence of holidays and pre-holidays, when the working day is reduced.
Example of working time calculation
Most businesses operate on a five-day work week. The formula for calculating working time is as follows:
Ntotal = Prv / 5 * Krd - 1 h * Kppd, where:
Ntot - standard working time;
Prv - length of working hours per week (40, 35, 36 or 24);
Krd - number of working days in the period (month, year);
Kppd - the number of pre-holiday days.
Example. There are 21 working days in April 2018. Standard working hours:
- 40-hour work week - 40 / 5 * 21 = 168 hours;
- A 36-hour work week is 36 / 5 * 21 = 151.2.
That is, those hours that will be worked in excess of 168 hours per week will be considered overtime and must be paid at an increased rate.
To correctly calculate the standard working hours for 2018, you need to refer to the production calendar. This document indicates that 1,970 working hours need to be worked in 2018. That is, on average, you need to work 164.1 hours per month.
Slightly different values for a 36-hour and 24-hour work week. With these schedules, working hours for 2018 are 1,772.40 and 1,179.60 hours, respectively.
Conclusion
Correct and timely recording of working hours allows you to correctly calculate wages for employees, taking into account overtime, illness, vacations and other things. Do not forget that labor inspectors first of all request the report card for inspection.
