Years, Days and Month
How many of you have struggled to find the number of years, months and days between two dates. I am sure many have which includes me. Specially when you need to be precise in your calculations.
Almost all of us have been doing this manually with three different calculations, fist for the years, then the number of full months from there and then the number of days. A brain teaser or may be brain buster to. In many times there could be little variances and is often over looked. What if there was a way to solve this through excel?
Yes Excel does have a function to do this, but the function is unknown to many as the function does not appear on the functions list. It is a hidden function called “DATEDIF”.
The dated if function has three components within the brackets. These would be start_date, end_date and unit. Thus the syntax would be as follows;
=DATEDIF(start_date,end_date,“unit”)
start_date refers to the first date that you need to consider for the calculation. This date should always be an older date then the end_date.
end_date refers to the last date that you need to consider for the calculation, and should always be a date newer than the first date.
unit refers to the unit in which the data should be reported in. This you could obtain as Years, Months or Days. Units also are represented by six different character combinations. These are,
“Y” — Would return a value of total completed years within a given start date and end date. Here the data will exclude any months and days that does not fall in to complete days.
“M” — Similar to the “Y” the “M” will return the total number of completed months between two dates.
“D” — Would return the total number of days between the set dates.
“YM” — Would return total number of completed month excluding any completed years, and any days that have not fallen in to completion.
“YD” — Would return total number of days between two dates, excluding the completed year.
“MD” — Would return the total number of days between two dates, excluding the completed months.
It is also important to note the calculation done always exclude the end date from the calculations in excel. We all know that if we are looking at number of days between 01/01/2021 to 31/01/2021 the value would be 31 days. However, if we deduct the start date from the end date in excel the value would return as 30, as in calculations there is a day exclusion. In order to calculate correctly, always add 1 additional day to the end day to obtain the correct calculation.
This formula is ideal to identify the age of people, or how a long a business has been in. Thus, the most frequent use of the formula as I see could be for Human Resource application. However, this also could be to see the number of days between number for delays delayed in settling a payment of how many days remaining to settle a payment in finance.
Lets look at how we could use this in a more practical approach with taking in to account Human Resource function. In HR you do need to have the calculation of knowing how old a person is from his date of birth a given date. This could be to identify who is up for retirement. Further, you could also use this to identify how long an employee has been in the company, in order to calculate gratuity payments if need be.
In order to calculate the gratuity component, I will be using the existing law for payment gratuity to an permanent employee with a fixed monthly salary as example. According to the law, any employee who has completed five years of services in a given company is entitle for gratuity, and he should be paid 1/2 month of the last salary drawn (at the time of resigning or retirement) in the multiplication of total competed years.
Lets assume that we do have both the date of birth and the date joined, and the fixed salary received by each of the employees in the business. The data would be as follows;
As the first step lets calculate the age of each employee with using the DATEDIF function. In order to ensure that the calculation is dynamic I will be using the TODAY function also. Further, the age calculation here would be done only to extract the completed years of an individual.
To obtain the number of years as the age, my first formula will be in the cell F5, where I would enter the formula as “=DATEDIF(C5,TODAY(),“Y”)”. In this formula “C5” refers to the start date, and TODAY() will let excel know that the end date should always be the system date when the file is opened. “Y” lets excel known that we only want the completed years to be extracted. Once you have entered the first formula, then copy it downwards to the rest of the rows in the line. Once the formula is entered and copied down, the table should now look as in the image below.
Now lets workout the number of years in employment, where we will once again use the same formula as we did for age, with extracting the fully completed number of years. All we need to now do is replace the cell address for the start date, which now need to be changed to “D5”. Once the formula is done and copied downwards the table should look as the one below.
Now in order to calculate the Gratuity payable as of today. Since gratuity is paid to those that have completed 5 years and above best way to calculate this would be to use a logical function of IF. All we need to do is see whether the value in cell G5 is greater than or equal to 5. The formula for this would be “=IF(G15>=5,E5/2*G5,0)”. Now the table should look like the one below.
Whilst I did calculate the full years completed as the age, if you do want to be precise with total number of years, months and dates, then you will need to break the age columns and create the formulas with changing the units to obtain the respective data, or create a text string, with nesting the DATEDIF functions in a CONCATENATE function or combine with & sing which ever you prefer. Please look at the table given below, with indicating the formulas for the 3 methods.
The first is to create formulas to be placed in three different columns where each on represents the year, month and day. Here the three formulas would be =DATEDIF(C5,TODAY(),“Y”) for years, and then =DATEDIF(C5,TODAY(),“YM”) to extract the competed months excluding completed years, and =DATEDIF(C5,TODAY(),“DM”) to extract the days removing the competed months.
The second two was to create a text string formula with the DATEDIF formula being nested within the text string formula. The two formulas are
=CONCATENATE(DATEDIF($C7,TODAY(),”Y”),” years”,DATEDIF($C7,TODAY(),”YM”),” months and “,DATEDIF($C8,TODAY(),”MD”),” days”)
and
=DATEDIF($C6,TODAY(),”Y”)&” years “&DATEDIF($C6,TODAY(),”YM”)&” months and “&DATEDIF($C6,TODAY(),”MD”)&” days”
The results with these would be as per the table indicate below.
Please note that using “DM” as a unit in the DATEDIF function need to be done with care, as sometimes it seems to derive erroneous values such as negative values. Thus, please do bear this in mind. However, I have still not encountered a situation such as this. Please do use this and if you do see its useful, do leave a comment.