Tip 047

The DATEDIF function:
Recently I received a message in my mailbox from Vanni who, gave me a formula that lets me calculate how many years of service an employee has put in.
The formula Vanni mailed me was correct, but only few will know this because it is not even listed in Excel as a function. This is the DATEDIF function.

Let me first start with what I was sent.
The person in context, Vanni, had emailed me the following formula: = (TODAY ()-B2) / 365
This formula does it perfectly but, why the decimal point?
They mean nothing to me.
I know, clicking on the “Decrease Decimal” button will eliminate it, but even then…

I find it more convenient to use the DATEDIF function.
I think there are many of you for whom this will be a bolt from the blue because, in Excel, you will find absolutely nothing on this feature.
So, unless you have followed the Excel Courses on swotster.com, whether you’ve purchased a good book or not, you can’t learn much about this feature, that is very useful.

Personally I think, the lessons of swotster.com which refer to this function are “superficial”, so I included this tip to make it a bit more extensive.

Here, you see a formula in which this function is used: = DATEDIF (B2, TODAY (); “y”)
The DATEDIF function requires a start date and an end date.

Here’s what you have to do:
As always, we begin, our formula with the equal sign (=).
Then we type the DATEDIF function and the opening parenthesis (
Then we type in the date of employment (start date), or the cell where this is stated. In this case, this is cell B2. Then we type a comma;
This is followed by the date till which you want to calculate service (end date).
In this example I use the TODAY function, which will give the number of years of “service” up till date.
So we type this function TODAY () followed by a semi-colon ;
Then we type a y in double quotes “y” and close the brackets ).
The y will give us the number of years between the start date and today’s date.
Today is 2/07/2012 so, 12 years is the correct number of years of service.

For your information:
If you enter an ‘m’ here, this will give the total in months.
If you enter a ‘d’ then, this will give the total in days.

Had this person started in August (1) of 2000, he would’ve had only 11 years (2).
This is still correct. Now we are in JULY 2012.

We can also show the number of extra months.
For this, type “ym” at the end of the formula.
By doing this, the number of additional months are shown, which remain after the subtraction of the entire year.

If you wish to see the number of extras days, type “yd” at the end of the formula.
This gives the additional number of days which remain after subtracting the entire years.

If you wish to see the number of extra days without the extra months, type “md” at the end of the formula.
This gives the additional number of days which remain after subtracting the entire months.

There are a lot of options.

Awesome!
You've completed Tip 047 START NEXT LESSON

Office 2007 - Excel Tips 2007 - 2013

Tip 047

Featured Courses