datedif problem

L

Legacy 65404

Guest
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...

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
I get 2 years:
Book1
FGHI
313/11/1986
323/10/1988
33730
Sheet1

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

does anyone know how to solve my question??

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

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

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

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

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...

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

Replies
12
Views
866
Replies
7
Views
646
Replies
2
Views
177
Replies
2
Views
334
Replies
33
Views
727

1,203,245
Messages
6,054,368
Members
444,720
Latest member
saathvik

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back