Excel allows you not only to perform operations with numbers, but also to calculate the intervals between dates and time intervals. This makes it very convenient to calculate seniority. Therefore, if an accountant masters this simple procedure, then in the future it will be very easy for him to make calculations.
Automated work experience calculation table
The future table, in which the length of service will be calculated, must have a certain header. It should indicate the columns in which intermediate and final calculations will be carried out. The hat looks like this.
Columns YEARS, MONTHS and DAYS highlighted with color, because it is in them that the final results that the accountant will need will be calculated.
Let's create several positions by filling in part of the table. Everything is clear with the full name, but when filling in the ACCEPTED and DISMISSED columns, you will need to change the format of the cells. In new versions of Excel, they change automatically if you enter the date in the standard way: HH.MM.YYYY. Just in case, it is better to change the format in advance. To do this, select all the cells in the "Accepted" and "Fired" columns (except for the table header), right-click to open the context menu and select CELL FORMAT (CTRL + 1) - DATE.
At the same time, we see that the date format can also be selected. Some people like it in numerical terms, others prefer the month to be written in words, etc. We will choose the partially verbal format.
Enter the start date of employees and their dismissal. Let them all get a job on different days and years, and quit on one.
Formula in Excel for calculating the experience RAZDAT
Calculate the number of days employees have worked. Because length of service is taken into account regardless of breaks (weekends, sick days and public holidays), you just need to subtract the start date from the end date. Excel allows you to do this. We write in cell D2 the formula \u003d C2-B2.
It turned out 6580 days. Let's translate them into years, and it's not enough just to divide by 365, because there can be 366 days in a year. Also, when we translate into the number of full months, excluding years, we should take into account the fact that one month can have 30 and 31 days. Therefore, for accurate analysis, you should use the special function RAZDAT:
- for cell E2(Years): = RAZDAT(B2;C2;"y");
- for cell F2(Months): =RAZDAT(B2;C2;"ym");
- for cell G2(Days): =DIFFERENT(B2;C2;"md");
Attention! This function is not contained in the function wizard and must be entered manually.
Table of parameters of the RAZDAT function:
Note! How can you elegantly calculate the final indicators of experience by summing up all periods of work at different jobs.
Self-calculation of your own work experience can take quite a long time. Fortunately, there are programs that can cope with this task and produce the desired result in a matter of seconds. One of them is the Calculation of seniority, which will be discussed in more detail in this article.
The calculation of the length of service, based on the date of admission and dismissal, quickly calculates the duration working period in one enterprise or another. The program can also calculate the total and longest continuous experience, it is enough to specify several working terms. If any date was entered incorrectly, it can be removed from the list.
Import and export
The program makes it possible to export the specified data to a separate file with the STJ extension. It will be saved in the location specified by the user. If you need to work with the saved data again, you can easily import it back into the Seniority Calculation.
Printing a Document
If there is a need to print out the specified data, the Seniority Calculation provides such an opportunity to the user. The sheet will indicate the name of the program, as well as all the information, including total and continuous work experience.
Advantages
- Russian-language interface;
- Free distribution;
- Availability of information on general and continuous experience;
- Ability to import and export data;
- Printout of entered information.
Flaws
- The program does not take into account the day of dismissal in the working period.
Seniority Calculation is a great program that can quickly give you the amount of seniority based only on the entered date of hire and dismissal. In addition, it makes it possible to save the specified data, as well as print them. At the same time, she loses one day in the calculations from each period, therefore, after the calculation, add the required number of them yourself.
You need to start the process with the preparation of documents. These include:
- employment history;
- labor contract;
- collective agreement.
Take the dates when you started working at a particular place, as well as the end dates labor activity, enter it into the calculation programs and get the result online.
Currently, this is a really convenient and accessible resource for everyone.
Its undoubted advantages are:
- simplicity;
- lack of special knowledge;
- counting speed;
- auto mode work.
The user is only required to enter the data known to him in special fields, and then click on the "Calculate" button. Further, the calculator will do everything by itself and provide the finished result.
You can use any calculator by entering the "Online seniority calculator" in the search box. Information about it can be obtained on the first pages of the search engine results. You can also use the sites mentioned above.
citizens Russian Federation calculate their length of service federal law"On labor pensions in the Russian Federation" No. 173, which entered into force on 01.01.2002.
At present, there is no concept of “work experience” in the law, since December 31, 2001 it has been replaced by the specified term “insurance experience”, that is, the period during which a working citizen has worked with his wages deductions to the Pension Fund of the Russian Federation, and other periods legally justified added to them.
However, the phrase "work experience" is often used interchangeably.
The insurance period is the duration of periods of work and other activities during which paid insurance premiums to the Pension Fund of the Russian Federation.
In addition to periods of work, the length of service includes the following periods:
- period of military service;
- caring for a child up to 1.5 years (if there are several periods, then the maximum period is 4.5 years);
- period of temporary disability with registration sick leave(see → sick leave calculation example);
- the period of registration in employment institutions when receiving unemployment benefits;
- care for the disabled, the elderly over 80 years of age (guardianship);
- period of illegal detention in places of deprivation of liberty.
An important feature calculation of the insurance period is the occupation of the type of activity that pays insurance premiums to the FIU. It doesn't have to be an organization individual entrepreneur independently transfers funds to the FIU.
study time in educational institutions, graduate school, courses is not included in the length of service, tk. insurance premiums are not paid to the FIU. The figure shows an example of the calculation of seniority.
The main calculation parameters are an indication of the date of acceptance and dismissal from work.
This is a small program that performs only one function - the calculation of work experience. It produces a result based only on the date of hire and dismissal. With its help, you can also find out the total length of service by summing up all periods.
Download OK | Seniority
Compared to the previous version, the Seniority Calculation provides much more options for the user. In addition to calculating the working period, this program creates a kind of report in the results.
In addition, the user can easily edit the field with the results of her work and give it the desired look. After this report can be copied to any text editor for further processing.
READ ALSO: Hotline SK SOGAZ, phone number and available assistance
Another feature is that with this application you can calculate the working period by indicating one year of work for several years of experience. Unfortunately, to get the total period of work using the Calculation of seniority, you will have to arm yourself with a calculator, since the program itself does not indicate this data.
Download Calculation of seniority
If in your work book not one entry, but several dozen, then it will be difficult to calculate the total length of service manually. Accounting programs usually have a seniority calculation module, but not everyone has a familiar accountant who can be approached with such a request.
There are also various online calculator s for calculating the insurance experience. But it would be more convenient for me to have such a calculator on my computer.
Writing such an experience calculator is quite simple. A program for calculating seniority can even be made in MsWord.
To write a program, it would be easier for me to use MSAccess, but not everyone has it. I also did not consider the final format of the exe file, since many are afraid to download and run unknown files on their computer.
Therefore, taking into account all these limitations, I chose Excel for the seniority calculation program.
I suggest you download a simple program for calculating seniority for free. The proposed program is an Excel file - calculator.xls
The file is located on Yandex disk, which excludes the receipt of any viruses.
Download the free calculator.xls file here.
The program for calculating seniority is free, you can use it without any restrictions. Forbidden to change free program and sell it.
To calculate the length of service, open the calculator.xls file and enter all periods of work on the work book in the cells with a yellow background, as seen in the screenshot below.
Enter the dates in the format DD.MM.YYYY (after entering, they are displayed as DD.MM.YY).
Dates are not controlled, that is, you yourself must monitor the correctness of the input. The order of the periods does not matter. If, when entering, you missed any period from the work book, there is no need to insert it in the middle. This period can be inserted at the end of the list.
When calculating, every 30 days is considered as a month, and 12 months as a year. It is also taken into account that the day of dismissal is included in the length of service.
In the cells on a green background, as you enter, data on the length of service for each period will appear, and on a light turquoise background under the inscription "total years months days" - the total length of service.
Make sure you have enough retirement experience under the new pension rules. After all, seniority for retirement can be earned without working. Read the instructions on how to get seniority for retirement without working.
READ ALSO: Sberbank list of accredited insurance companies -
The procedure for calculating the length of service according to the work book manually:
- write out all periods of work according to the work book,
- calculate the number of days, months and years for each individual period of service,
- calculate the total length of service by summing the length of service for all periods.
An example of calculating seniority
Consider the calculation of seniority on an example.
Let's take the following period of work according to the work book: hired on 08/04/1984, fired on 08/02/1996.
You need to start calculating the experience from the days. From the day of dismissal, we subtract the day of employment and add one, since the day of dismissal is also considered a working day, that is, 2 - 4 1 \u003d -1.
But since the result is less than zero, you need to take a unit of months, and this unit of months is equal to 30 days.
We get 30 2 - 4 1 = 29 days
Now let's count the months, remembering that we took the unit when counting the days, that is, we need to subtract 1: 8 - 8 - 1 = -1 from the months.
Since the result is again less than zero, you need to take one from the number of years, that is, take 12 months.
As a result, we get 12 8 - 8 -1 \u003d 11 months
Now we count the years, remembering that we took the unit when counting the months 1995 - 1984 -1 = 11 years
As a result of the calculations, it turned out that the length of service for the period was 11 years 11 months 29 days.
If when calculating the days it would turn out 30 or 31, in this case it would be necessary to convert this number of days into full months, that is, subtract 30 days and add one to the number of months.
The total experience is determined according to the same rules by adding days, then months and years of experience for all periods.
For example, let's take 2 periods: accepted - 08/04/1984, dismissed - 08/02/1996 (experience for a period of 11 years 11 months 29 days) accepted - 09/15/1997, dismissed - 10/25/2001 (experience for a period of 4 years 1 month 11 days).
Let's sum up the days first: 29 11 = 40.
Since 30 days are considered a full month, subtract 30 from the amount received 40 - 30 = 10 days.
Let's take into account that it will be necessary to add 1 to the number of months.
Sum the months 11 1 1 = 13.
It turned out more than 12 months. Since a full year is considered 12 months, subtract 12 from the number of months and add one to the number of years.
So, months will turn out 13 - 12 \u003d 1 month
Let's sum up the years: 11 4 1 = 16 years.
In total, the total length of service was 16 years 1 month 10 days.
As you can see, calculating the total length of service manually is quite time consuming, and it is easy to make a mistake.
CALCULATION OF EXPERIENCE
To calculate work experience, you can use different ways.
Each of them is convenient and has its own characteristics. Among them:
Calculation of periods of work (service, activity) is carried out in calendar order based on full months (30 days) and a full year (12 months). At the same time, every 30 days of these periods are converted into full months, and every 12 months of these periods are converted into full years (clause 2, section III of the Rules for calculating and confirming the insurance period).
If the periods of work (service, activity) included in the insurance period coincide in time, one of such periods is taken into account at the choice of the insured person, confirmed by an application that indicates the period chosen for inclusion in the insurance period (clause 22 of the Rules for calculating the insurance period) .
