In any working environment, there are various instances where you would be required to calculate the duration between two dates.
A human resource officer would need to know how long a given employee has worked with the company or the actual age of the various employees for planning purposes. In an automotive industry, you would need to compute how long, say a given vehicle has taken to be serviced. For most NGOs, it would be needful to know, based on the contractual terms of various consultants, how many months/years are remaining and subsequently, how many employees have their contracts ending, say in the next 3 months, 6 months, and so on.
There are many ways that you can achieve to calculate the difference between any two dates in Excel.
For this example, we shall discuss how to calculate the length of service of various employees as at a given date, but the concept applies in all the scenarios described above.
Calculate the duration of service of employees
We have the following subset of data extract.
In cell C2 we shall type a date as at which we shall compute the length of service. This can be (1) a date that you hardcode, or (2) a date representing today’s date, or (3) a formula that returns the end of the previous month with reference to today’s date.
To always use the current (i.e. today’s) date, type the following in cell C2:
=TODAY( )
The Excel’s TODAY function will always return the current date.
To always use the end of the previous month with reference to today’s date, we shall type the following in C2:
=EOMONTH (TODAY( ), -1)
The EOMONTH function normally returns a serial number that represents the end of the month, “x” number of months before or after the specified date. The number of months before or after can be a positive number or a negative number or zero if you want to return the end of the current month. For instance, if the TODAY function wrapped inside this formula is assumed to be “8/8/2017” then using 0 as the last parameter will result in “31/8/2017”, using -2 will return “30/6/2017” whereas using a +4 will yield “31/12/2017”
In this example, we shall enter this EOMONTH function in cell C2 to always consider the end of the previous month.
Different approaches to compute differences between two dates
Let us explore various methods to calculate the duration between two dates.
Method 1: Subtract the two dates
The obvious method I have seen with various trainees is to subtract the date of joining from the specified end date and then divide the resultant value by 365 (or 360 for other regions) as shown below.
The equivalent of the formula shown in cell E5 above would be = DAYS ($C$2, D6)/365 which would give similar results.
Method 2: Subtract the years
In this approach, we assume we just need to compute the difference between the two dates as whole years, as is the case when you want to compute your age as at the last birthday.
We therefore employ the formula shown in the figure below.
This approach rounds the result to the nearest whole number.
Method 3: Using the YEARFRAC function
This method gives similar results with the first method above. That is, it will compute the fractional year differences between two dates.
For instance, if the last day is “31/07/2017” and you joined the company on “27/09/1998” then the formula will compute the difference taking into account differential months. So, whereas method 2 would give 19 as the difference in years, YEARFRAC will yield 18.84.
Take a look.
Notice that in this case you start by specifying the date of joining in the formula.
Let us compare the results
The figure that follows compares the output of the various approaches discussed so far.
Method 4: Using DATEDIF function
When you type DATEDIF as you would do with any other formulas, this formula doesn’t show a prompt (normally called the intellisense) as other formulas do. It was popular in earlier Excel versions 2003 and earlier) but has since not been given a lot of focus. I guess Microsoft might stop supporting it very soon (my guess).
This function computes the difference in either the completed/full years, months or days, between two dates. Just like the YEARFRAC function, it also requires you to specify the starting and the ending date. However, you would need to specify the time unit of output, in terms of years, months or days.
Syntax: DATEDIF (Start_date, End_date, Unit)
There are six different outputs that you can get based on the time unit you specify as follows:[/vc_column_text][gem_table]
Unit | Output |
|---|---|
| “y” | Difference in terms of complete years |
| “m” | Difference in terms of complete months |
| “d” | Difference in terms of complete days |
| “yd” | Returns the difference of complete days, ignoring the ones in the years that have been completed. Simply put, for the specified start date, it takes the equivalent date in the immediate previous year as the basis of subtracting the completed days |
| “ym” | Returns the difference of complete months, ignoring the ones in the years that have been completed. It takes the immediate previous year as the basis of subtracting the full months |
| “md” | This interval just subtracts the day part of the two dates. For instance, in the table below, notice the “md” column is the difference between the day element of the two dates. In row 9 for example it is 31-1=30 |
The figure below illustrates all the six possible outputs.
In conclusion
The various approaches discussed above are enough arsenals for various scenarios you may encounter. Should you have comments or questions regarding manipulating dates in Excel, please lodge them in the comments area below.
Please share the article with your colleagues and let them improve how they work with Excel.
