Program for calculation the seniority of work in Excel download
Excel allows not only to perform operations with numbers, but also to consider the interval between dates and time intervals. Because of this, you may to count the seniority very easy. So if an accountant masters this simple procedure, in the future it will be counted so simply.
Let consider step by step how to calculate the seniority of work in Excel and learn, how to make the template for calculation of service that you can use it always.
The automated table of calculation of seniority employees
Our future table, which will be carried out calculations of seniority are, should has a certain heading. It must include the columns which will hold the intermediate and final calculations. The heading of the table looks like:
| Full name | Place of work | Adopted | Dismissed | Days worked | Years | Months | Days |
The columns of YEARS, MONTHS and DAYS are highlighted in color, because the end results of required accountants will be counted in them.
Let create to several items by completing a part of the table. With the FULL NAME all is clear, but when filling in the columns are ADOPTED and DISMISSED you need to change the format of cells. In the new versions of Excel, they are changed by automatically if you enter a date in the standard manner: NN. MM. YYYY. As a precaution, it is better to change the format beforehand. To do this, you need to select all cells in the columns «Adopted» and «Fired» (in addition to the head of the table), right-click context menu and choose FORMAT OF CELLS or select: «HOME»-«Cells»-«Format»-«Format Cells» or press (CTRL+1):
Thus we see that the date format can also be selected. Some of specialists like in numerical terms, another ones prefer to have the month spelled out the word, etc. We will choose is partly verbal format.
After that you need to enter the start date of commencement of the work employees and their dismissal. For example, all of them settled down to work in different days and years, but were resigned in one day.
The formula in Excel for counting of experience DIFFDAT
Let calculate to the number of days which employees were worked. Inasmuch as the seniority is taken into account, regardless of the breaks (weekends, medical certificates and holidays), you just need to subtract from the end date of the initial Excel and to record in the cell D2 to the formula =С2-В2.
It was turned 6580 days. Let transform them in years: it is not enough just to divide this sum by 365, because a leap-year is included 366 days. So when we transform into the number of full months excluding the years, it should be taken into account to the fact, that one month can has 30 and 31 days. Therefore, for accurate analysis, you should use to the special function of DATEDIF:
- for the cell E2 (Years) =DATEDIF(C2,D2,"y");
- for the cell F2 (Months) =DATEDIF(C2,D2,"ym");
- for the cell G2 (Days) =DATEDIF(C2,D2,"md").
Attention! This function is not contained in the function wizard, so it needs to be entered manually.
The table of options DATEDIF:
| Parameter | Description |
| "d" | the number of complete days |
| "m" | the number of complete months |
| "y" | the number of complete years |
| "ym" | the number of full months, excluding the years |
| "md" | the number of days excluding months and years |
| "yd" | the number of days excluding years |
Download program for calculating seniority in Excel
Pay attention! You will be able calculate elegantly to the total score of outcome indicators experience, if you summing up all periods of work for different workplaces.
