Formulas for Calculating Work Experience Periods in Excel

The simplest way to calculate age or work experience is through the use of the =DATEDIF() function. This mysterious function was not documented in the Excel help for a long time. Moreover, it is not listed in any formula list, so to use this function, you need to enter it manually in the formula bar or directly into the cell. Despite this, it has been available since Excel versions from 2000 and performs its main task perfectly.

Examples of Formulas Using the DATEDIF Function in Excel

Thanks to the DATEDIF function, it is easy to perform complex calculations for comparing dates. The main difficulty lies in the fact that every 4 years there is a leap year, and then the number of days in a year changes from 365 to 366.

To calculate age from the date of birth using the DATEDIF function, use a formula similar to this one:

For calculating work experience, apply the following formula:

The DATEDIF function returns the number of days, months, or years that have passed during the time period between two dates. To work with the function, fill in the 3 mandatory arguments:

Start date.
End date.
Date format - in which time unit to calculate and display the difference between the start and end date ("y" - year, "m" - month, "d" - day, etc.).

The list of all difference display formats between dates is given in the following table:

No.	Format Code	Returned unit of time measurement
1	"y"	Number of full years in the specified period.
2	"m"	Number of full months between two dates.
3	"d"	Number of days.
4	"md"	Remaining day difference between the start and end date without considering years and months.
5	"ym"	Number of remaining months between dates without considering years and days.
6	"yd"	Remaining day difference between the start and end date without considering only years.

It is worth noting right away that the 3rd format option is not particularly useful because when subtracting the younger date from the older one, we get the same result in days through regular mathematical subtraction. For example, two ways to find out how many days have passed between two dates:

After all, in Excel, like in all other programs included in the MS Office package, the 1900 system is used for date numbering. And each date is the number of days that have passed since 01.01.1900.

Special attention should be paid to the 4th, 5th, 6th codes. These codes allow you to get the residues of months of incomplete years and the residues of days from incomplete months. It is easier to show their principle of operation through an example than to describe it. Pay attention to how the function behaves with these codes in its third argument.

To calculate the exact pregnancy period from the conception date to the child's birth date, it is better to apply such a formula:

And to calculate the conception date by childbirth, we do not need to use the DATEDIF function:

Calculate conception date by childbirth.

Example of a more complex formula for DATEDIF in combination with other functions to convert the fractional part of the number of years into months and days:

=INT(72/B2)&" years "&DATEDIF(1,(72/B2-TRUNC(72/B2,0))*365+1,"YM")&" months "&DATEDIF(1,(72/B2-TRUNC(72/B2,0))*365+1,"MD")&" days"

How to Calculate the Number of Incomplete Years Between Two Dates in Excel?

There are other ways to create formulas to calculate the difference between dates without using the DATEDIF function:

=ROUNDDOWN(YEARFRAC(A2,A4),0)

Now, if we need to take into account incomplete years of car operation, we need to use a different formula:

As seen in the example, the DATEDIF function does not solve any computational tasks with dates. Sometimes it is necessary to resort to other narrowly focused functions.

Example of Practical Application of the Formula with the DATEDIF Function

Very often, you have to use the DATEDIF function to sign data visualizations based on pivot tables and the TimeLine filter type. When using the filter, the range of date samples is visually visible. But if you need to accurately present the size of the range of the selected accounting period, then it is better to add a dynamic signature based on a formula with the DATEDIF function:

download file Download

Now, when choosing a different number of quarters, the dynamic formula automatically calculates the number of selected months using the TimeLine tool.