"srinivasan" wrote:
From 31-10-2008, I have to deduct 1-11-1969. It may be seen that
the difference is 39 years. But in excel it shows 39 years 12 months
and 30 days when I use date(year,month, day) formula.

Pete already pointed you to DATEDIF, which may or may not be what you need.

However, for my edification, please show the exact formula or method by
which you coerced Excel to show 39 years 12 months 30 days. Or is "39" a
typo?

I don't know what you mean by "date(year,month,day) formula". Do you mean
DATE(2008,10,31) - DATE(1969,11,1)?

That results in a number of days (14244). It should not be interpreted as a
date value and formatted as d-m-yy.

Of course, you can format a number any way you please; but it is not
necessarily meaningful in that format. For example, I hope we can agree
that Percentage would not be a meaningful format here; but it can be done.
Likewise, elapsed days (14244) is not meaningful when formatted as a date
value.

Formatted as d-m-yy, the number 14244 appears as 30-12-38, not 30-12-39.
And that is indeed the date corresponding to 1/1/1900 plus 14244. Its
closeness to the expected number (38 years 11 months 30 days) is just a
coincidence.


The result should show 39 years, 0 month and 0 day.

Only if you are computing the difference "inclusively". That might be
appropriate for reporting years of service based on starting and termination
dates, for example.

DATEDIF does not normally do that. But it might be sufficient to fudge the
end date (end+1). (Caveat: I have not tried all combination of start and
end+1 dates to see if that might result in some surprises.)

However, there is no uniform way to report elapsed time in years, months,
and days.

Another common method is to assume that a year is 365 days (or 365.25) days,
and a month is 30 days (or 365/12 or 365.25/12). Then, for an inclusive
difference:

total days in A1: =DATE(2008,10,31) - DATE(1969,11,1) + 1
years in A2: =INT(A1 / 365)
months in A3: =INT((A1 - A2*365) / 30)
days in A4: =A1 - A2*365 - A3*30

This might be done for financial analysis, for example. However, it would
be better to leave the difference in days (A1).

This latter approach might also be necessary if you want to perform other
arithmetic on the elapsed days.

For example, in another thread, someone wants to compute the average of the
elapsed days for an array of start/end date pairs. That is difficult to do
if you use DATEDIF for the computation. In fact, I am not sure there is a
"right way" to do it in that case.


----- original message -----

"srinivasan" wrote in message
...
I have to deduct between two dates. From 31-10-2008, I have to deduct
1-11-1969. It may be seen that the difference is 39 years. But in excel it
shows 39 years 12 months and 30 days when I use date(year,month, day)
formula. I want the result to be shown in three colums adjacent one for
year,
another for month and the last for day. The result should show 39 years,0
month and 0 day. What formula I must use to get this result in three
column
cells.

Further I have to multiply the result years in to half years. When I
multiply by 2 two instead of 78 it shows calculation by year number i.e
29218. What formula to be used to show it as 78 half years in another cell
to
be used for calculation.
Thanks for the help.