datedif problem

[Legacy 65404]

Hi all... I think I have a problem....

I used a datedif between the dates: 03/11/1986 and 03/10/1988, and it returned me 1 year, 11 months and 28 days!!! (it should return 29 days!!)

when I tested, I put between 03/11/1986 and 03/11/1988 and returned 2 whole years, what is correct...

It may have happened because 1986 is a leap year... but I don't know what to do...
do you guys have any clue??

thx all in advance...

(please help...)

 

[orbea_adam]

I get 2 years:

Book1
	F	G	H	I
31	3/11/1986
32	3/10/1988
33	730
Sheet1

 

[Legacy 65404]

but shouldn't you get 2 years only in 3/11/1988?

 

[Legacy 65404]

does anyone know how to solve my question??

 

[mortgageman]

datedif (using the "d") interval gives me the same result as subtracting the two dates (which of course it should). What exactly do you think is wrong?

Gene, "The Mortgage Man", Klein

 

[Legacy 65404]

first... I was mistaken about being leap year... sorry... it's not the problem...
let me see if I can explain what happened:

I use 3 cells, one for year, one for month and one for day
year: =datedif(03/11/1986,03/10/1988,"y")
month: =datedif(03/11/1986,03/10/1988,"ym")
day: =datedif(03/11/1986,03/10/1988,"md")

and it returned like this:
year: 1 (right)
month: 11 (right)
day: 28 (wrong) - should be 29...

when i put:
year: =datedif(03/11/1986,03/11/1988,"y")
month: =datedif(03/11/1986,03/11/1988,"ym")
day: =datedif(03/11/1986,03/11/1988,"md")
(between 03/11/1986 and 03/11/1988)

it comes:
year = 2
month = 0
day = 0

I dont know if I could be clear here... sorry if I didn't...
and thanks for the help

 

[mortgageman]

First of all, why are you saying the 11 is right? I mean it is, in the sense that datedif is returning what the "documentation" says it should, but there are clearly more than 11 months in the interval that you choose.

The only way you are going to get consistent results is the way I did it. Use the "d" interval - that will be the same as subtracting the two dates.

Gene, "The Mortgage Man", Klein

 

[barry houdini]

This is the behaviour I would expect from Datedif

If you use

=datedif(03/11/1986,03/10/1988,"md")

then 23 whole months from 03/11/1986 will take you until 02/11/1988

Because 1988 is a leap year then there are 28 days from 02/11/1988 until

03/10/1988, hence formula returns 28

 

[Legacy 65404]

yap barry... I knew I had a leap day... lol...

but how can i do? I need the cells to be like the whole date, so that's why I used three different uses of datedif,
I need it to return - 1 year, 11 months and 29 days passed...
do i have to check if its a leap year and add 1 day? this is what I was afraid... more things to think...

 

[mortgageman]

You want to use the "y", "m", and "d" intevals and then subtract from the months the number of months in the year interval and subtract from the days the number of days in the y and m interval (this part is where the leap year logic comes in)

I'm off to "shul" but I will check later, if no one posts a formula by tommorow I will.

Gene, "The Mortgage Man", Klein