if we are given birth date (eg 12-june-1993), we need to calculate the average age as at 1 jan 2013. (note: if there are like 100 records of different birth dates, how to calculate the 100 records in the shortest possible method?)
I know of one way.. that is to select the column which I place 1 jan 2013 MINUS the birth date and DIVIDE by 365.25... but if there are like 100 records.. I cant possibly use this method one by one right??? is there a short cut??


another question.. if to calculate the average length of service an employee has been working in a company.. the joined date is given in this format (eg 2-sep-1990) and there are like 100 records of different employees.. how to calculate??? note: in the computation allowed to use 30days = 1 month

i'm not proficient in Microsoft excel.. so im trying to find out... thanks.

if we are given birth date (eg 12-june-1993), we need to calculate the average age as at 1 jan 2013.
...
calculate the average length of service an employee has been working in a company ... the joined date is given

If column A contains dates, then
=ROUND(AVERAGE(A:A),0)
is the average of the dates.

So if they're birthdays, then
=DATE(2013,1,1)-ROUND(AVERAGE(A:A),0)
is the average age as at 1 jan 2013, in days. Format this as a number. To get age in months or years, divide by the appropriate value and round as needed .

Likewise, if they are "joined" dates, then
=TODAY()-ROUND(AVERAGE(A:A),0)
is the average length of service, in days.

Hi, thank you for your reply.

I tried "=TODAY()-ROUND(AVERAGE(A:A),0)" where A:A contains the joined dates of lets say 100 different employees in the format of (dd-mm-yy). I pressed cltr-shift-enter (I tried enter but it gave me dd-mm-yy as the final answer) and I got 4digit number.. which is the total average length in days right.. hmm. I just wonder what's the '0' at the end stands for? thanks again

I just wonder what's the '0' at the end stands for?

From Excel's built-in "Help" for the ROUND function:

ROUND(number, num_digits)
number: The number that you want to round.
num_digits: The number of digits to which you want to round
the number argument.

So num_digits=0 means "return a value having no digits after the decimal point." In our case, that's complete days with no extra hours or minutes. If we were dealing with dollars and cents, we might use 2 instead of 0.

I pressed cltr-shift-enter (I tried enter but it gave me dd-mm-yy
as the final answer)

Dates in Excel are really numbers, formatted differently. Using just "Enter" by itself actually gives the right answer, but to change the format select the cell and use
right-click Format cells number
to see the format choices.

By changing the format of the value 41353, for example, you can get it to look any of these ways and many mo
41353
03/20/13
41,353.000
4135300.00%
Wednesday, March 20, 2013
3/20/13 12:00 AM
4.14E+04
€41,353.00

Ragardless of the format, the value behaves the same way in arithmetic calculations.

oh wow I didn't know about this! to think I even wanted to try cltr-shift-enter.. hahaha that will just give me another answer.

thank you so much!!