date function

[srinivasan]

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.

 

[Pete_UK]

Have a look at Chip Pearson's site here:

http://www.cpearson.com/excel/datedif.aspx

where he describes the use of the DATEDIF function.

Hope this helps.

Pete

 

[Gord Dibben]

DATEDIF returns 38 years, 11 months 30 days from OP's dates.


Gord Dibben MS Excel MVP

 

[JoeU2004]

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]

Thanks for your response
I have used DATE(YEAR(B8)-YEAR(B7),MONTH(B8-B7),DAY(B8-B7)) in three
adjacent column cells to get the result,

year, month, day separately (not together).I have to use the result year,
month day in a separate formula to

arrive at a solution.Hope I have cleared now..

 

[srinivasan]

Thanks for your response
I have used DATE(YEAR(B8)-YEAR(B7),MONTH(B8-B7),DAY(B8-B7)) in three
adjacent column cells to get the result,

year, month, day separately (not together).I have to use the result year,
month day in a separate formula to

arrive at a solution.Hope I have cleared now..

 

[JoeU2004]

I have used DATE(YEAR(B8)-YEAR(B7),MONTH(B8-B7),DAY(B8-B7))
in three adjacent column cells to get the result

Arguably, what you should have done is:

DATE(YEAR(B8+1)-YEAR(B7), MONTH(B8+1)-MONTH(B7), DAY(B8+1)-DAY(B7))

since you want an "inclusive" difference.

But that does not always work anyway. Consider when B8 is 31-10-2008 and B7
is 1-11-1969. It has the same result as when B8 is 30-10-2008. Use Tools >
Formula Auditing > Evaluate Formula to step through the calculation to see
why.

Moreover, I presume that you formatted the three cells with the custom
formats y, m and d respectively.

But formatting only changes the appearance of numbers. It does not change
the actual value. If all three cells have exactly the same formula, as I
suspect, they all result in the same value. Format the cells as General to
see that number. With your formula, it is probably 14609.

This will give you problems if you reference those three cells in other
computations, expecting just years, months and days respectively.

Whether or not you understand all that, simply follow Pete's suggest with my
embellishment. If you want an "inclusive" difference, compute:

years: DATEDIF(B7,B8+1,"y")

months: DATEDIF(B7,B8+1,"ym")

days: DATEDIF(B7,B8+1,"md")

Caveat emptor: Some MVPs have indicated that DATEDIF is broken in some
updates of Excel 2007. I don't know anything about that.


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

 

[Ron Rosenfeld]

days: DATEDIF(B7,B8+1,"md")

Caveat emptor: Some MVPs have indicated that DATEDIF is broken in some
updates of Excel 2007. I don't know anything about that.

It seems to be broken in Excel 2007 SP2+, at least with regard to the "md"
parameter.
--ron

 

[JoeU2004]

It seems to be broken in Excel 2007 SP2+, at least with
regard to the "md" parameter.

Wunnerful! How is it broken? Results in an error? Or bad numbers?

If the latter, are the numbers always bad? Or just certain cases?

Can you post some examples?

Just curious....

 

[Ron Rosenfeld]

Wunnerful! How is it broken? Results in an error? Or bad numbers?

If the latter, are the numbers always bad? Or just certain cases?

Can you post some examples?

Just curious....

from:

http://groups.google.com/group/micr...f5100b56?hl=en&q=datedif+122#39e2669af5100b56


=DATEDIF(DATE(2009,6,27),DATE(2012,1,5),"md")

In 2007, this gives me 122. This happens all the way up to the point where
the second date is 1/26/2012 and then it hits zero at 1/27/2012.


And it is the case on my Excel 2007 SP2 also
--ron

 

[srinivasan]

Thanks Mr JoeU and Ron Rosenfeld. Both works nice and thanks a lot for
sharing your views which helped me solve a problem and also enrich my
knowledge on this formula. Thanks a lot to every body.
Regards