Free Microsoft Excel 2013 Quick Reference

Calculating average tenure (in years and months)

Hey! I'm a newbie, so i apologize in advance.

I've got a spreadsheet of the company's tenure in years and months that I need to find the average. Can anyone show me how to do it?

Here's a sample of my spreadsheet.


Post your answer or comment

comments powered by Disqus
i am trying (unsuccessfully) to calculate how old my students are in years and months. i have used the YEARFRAC, but it calculates on base 10 not base 12. i tried DateDiff - which didn't work at all. HELP!

The attached is my effort at calculating the age of the equipment in column A.
That is, how long between the date in column B and 'today' expressed in years and months.
I have a decimal year adnd days, but these are not what I need.

As ever, any help on this is appreciated.


Hi all,

I was asked by a colleague how they could put a persons date of birth in one cell, todays date in another, and return their age in years and months. Accuracy to within a month. This is what I gave them.

=TRUNC((B1-A1)/365.25)&" Years "&ROUND(((B1-A1)/365.25-TRUNC((B1-A1)/365.25))*12,0)&" Months" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Just wondering in there is a more efficient way of doing this.




I trying to find a formula to give the difference between two dates in year and month.

For example, the start date is Feb 1 2006 and end date is Jan 1 2008.

The formula should result in 2.11

I tried the following formula from a previous thread but it gives the answer in text but I need it to result in 2.11.

Apperciate help.

Can anyone help me with calculating the difference between a hired date and today's date, as length of service in years and months?

How To Convert Months In Year And Month Format


It might be a stupid question to ask but can it be possible to get a formula by which we can get date difference in Years and months? for example 3/22/2009 - 4/22/2010 = 1 year 1 month. Currently I am using =DATEDIF(I2,TODAY(),"m")/12 to get years in decimals but wanted to format this result fields.



In Excel 2003, I want to calculate the number of years and months employed
using a hire date and the current date. i.e.
06/28/2006 – 06/25/2005 = 11 months
06/28/2006 – 06/25/2004 = 1 year 11 months
06/28/2006 – 08/01/2000 = 5 years 10 months
06/28/2006 – 04/01/2000 = 6 years 2 months

Somebody must have done this before, yes?


I have two sets of data in the date format that I need to calculate the number of months and years in between, the second date will always remain the current date I tried using =today() to keep the current date according to the system date and time

15/02/1977 to 11/10/2007
I need to use a formula that will calculate the number of years and months between these dates .
Any help will be appreciated


How do we get excel to calculate the years and month between 2 dates?

eg.. Start - 15/03/2000 and End - 20/04/2010

I would like to calculate the number of years and months that have passed since a certain date. Would like it in a number format so I can pickout those who have gone reached 5 year increments during each month.

Such as someone reaching 40 injury free years in June of this year I can let them know.

I have attached a file containing staff details. Please I need a formular that will return length of service and age in years and months like 3yrs6months for 3.5 years.

Please if this is not possible, I will appreciate anything close to it.


How do I calculate differences in dates in months and years? I can do days,
months, and years, but not a combination.

using =DATEDIF(BH2,AB2,"y") &" years," & DATEDIF(BH2,AB2,"ym") &" months," & DATEDIF(BH2,AB2,"md") & " days"

displays an age calculated from dates in BH2 and AB2 in a neat format in a 3rd cell where BH is the clients DOB, AB the date they were 1st seen in a clinic.

What I want is to be able to enter similar pairs of dates into BH3 & AB3, BH4 & AB4 etc and then determine the average age of a group of clients when they first came to the clinic.

Any ideas anyone?



I am working with data that has been entered representing ages using a decimal point i.e. 7 years and 6 months is 7.6 and 8 years and 11 months is 8.11. I want to know if I can average this data? I'm assuming I'm going to run into problems given that the months aren't represented in a base10 system??? The ages given are raw data and I cannot go back to rewrite by subtracting two dates as they are abstract 'scores'.

I'm not even sure if I am explaining this well, go slowly with any replies as my excel knowledge pretty much only covers adding, subtracting and formatting the cells to make them look nice!

Thanks for any help!

Hi All,




15 DEC 06 15 JAN 07 1.01 (1 YEAR 1 MONTH)




I need to produce a developmental profile on our students that lists tests across the bottom of a chart and the score they received within the chart. The score has to be in years.months (ie 3.10) format. I have fiqured out no way to have excel understand above something like 3.9... it thinks 3.10 should be 3.1 and charts it that way. Any help would be really appreciated.

I'm doing some analysis regarding length of employees service but can't find
any thread relating to a formula that may help do this specifically.


Employee A started working on 01/01/2000 and resigned on 01/02/2009

The start date is in e.g. cell A1 and the resignation date is in e.g. cell A2

Can someone give me a formula that will basically deducted one date from the
other date and tell me how many years and months they worked for us? e.g. the
answer I would like to see is 9 (for the years) and 1 (1 for the month).

If you can, please be very simplistic with your explanation as I don't know
excel very well and tend to stumble around with any formulas I do use.

Thanks in advance

How do you calculate age by a birth date I need years and months... if the
person is not a year old I need to know how many months... if the person is
1yr 2mo I need it to return 1yr 2mo... if the person is only 2mo I need it to
return 2mo.. is there any kind of formula that could work...

Thanks Donna

New to forum - need some help

Having problems with SUMPRODUCT formula. I want to get a total by year and month of many columns and rows.

Data looks like this.
sheet 2
col a col b col c col d col e col f col g
row # year month tran1 tran 2 tran 3 tran 4 tran 5 etc
1 2007 1 10 15 20 25 30
2 2007 1 8 10 12 14 16
3 2007 2 3 6 9 12 15
4 2007 2 2 4 6 8 10
10000 2007 12 1 2 3 4 5

I want to Calculate in a different worksheet

Sheet 1
row # col a col b col c
1 2007 1 (total of column c through g if yr and mth match)
2 2007 2 (total of column c through g if yr and mth match)
3 2007 3 (total of column c through g if yr and mth match)
4 2007 4 (total of column c through g if yr and mth match)

SUMPRODUCT works fine if the all the conditions and sum rows are the same. As soon as I try to add multiple columns i get a #VALUE error.


Can anybody help me with a VBA function that willl calculate Fiscal Year and month.

Considering that the date is in column A in the format 5/27/2010 I need in column C to be returned FiscalYear/FiscalMonth 11/02

(Fiscal Year: April - March)

Thanks a lot!

I need a formula to work out an expiry date from today, using a given number of years and months.

For example, today is 15th July 2003, using 5 years and 5 months I would want December 2009 to be returned. Using 0 years and 1 month I would want August 2003 to be returned.

At the moment I am using something like:


It works fine - but it's not 100% accurate as there aren't 30 days in each month, and sometimes there are 366 days in a year. Does anyone know a better way to do this.

Example file attached to help.

Thanks for looking.


I have the following data;

2008-06-03 2008-07-03 2008-08-03...2012-06-03 2012-07-03
0 1 2 ... 5 6
0 2 3 ... 6 7
3 3 3 ... 1 2

(the formating is wrong in the post but ffor the top line 0 corresponds to 2008-06-03 and 6 is for 2012-07-03)

I then want to look up the following dates inorder to get the corresponding number.


The first one should retrieve 1 and the third one should retrieve 3. And now to the tricky part. I want the second one to retrieve the 2 figure. I want to base the Lookup function on YEAR and MONTH and not take into consideration the DAY.

The problem is when I use HLOOKUP I get it to work but the second one will not work since it do not find the date it will not retrieve any figure.

Can I use DATE? Any suggestions how to use the HLOOKUP function but only to focus on YEAR and MONTH.

Many thanks, Mogli

If I have a cell that has a number, say 59. I would like to convert that in another cell into years and months, for example 59 months would equal 4 years 11 months. Is there a way to display that as 4.11

No luck finding an answer? You could always try Google.