Bug? Using DATEDIF function

[Steven]

In a Worksheet I've used the formula =DATEDIF(A3,A4,"D"), with A3 and A4
being example cell addresses.

The 1st and 2nd arguments point to cells in which a date has been input.

I then tried to divide the result by 365, so amended cell contents to
=DATEDIF(A3,A4,"D")/365 however instead of the number of days between
two dates being divided by 365, the result returned is 1/1/04. If I then enter
the cell and backspace over /365 a date is returned, instead of going back
to the number of days between dates.

My questions are:

(1) Should Excel be behaving like this or is this bug?
(2) To divide the result of the formula by another number (eg. 365) should I
be doing something different? I've tried =(DATEDIF(A3,A4,"D"))/365 which
doesn't work.
(3) Why doesn't DATEDIF appear in the Insert>Function... menu?

Any tips appreciated.

Steven

 

[Domenic]

Hi Steven,

Format the cell containing your formula as "General". That should give
you the result you're looking for.

(3) Why doesn't DATEDIF appear in the Insert>Function... menu?

Good question!

 

[JE McGimpsey]

In a Worksheet I've used the formula =DATEDIF(A3,A4,"D"), with A3 and A4
being example cell addresses.

The 1st and 2nd arguments point to cells in which a date has been input.

I then tried to divide the result by 365, so amended cell contents to
=DATEDIF(A3,A4,"D")/365 however instead of the number of days between
two dates being divided by 365, the result returned is 1/1/04. If I then
enter
the cell and backspace over /365 a date is returned, instead of going back
to the number of days between dates.

My questions are:

(1) Should Excel be behaving like this or is this bug?

"Should" is too strong a word, but it's not a "bug". "Does by design" is
more appropriate, but it works for, say, =A1 + 1, which if A1 contains
a date, should probably return a date.

One thing you need to be aware of is that what is stored in the cell and
what is displayed are two different things. A date is stored an integer
offset from a base date (format any date as General to see the number).
If the cell has a date format, the display engine then formats the
integer to look like a date.

(2) To divide the result of the formula by another number (eg. 365) should I
be doing something different? I've tried =(DATEDIF(A3,A4,"D"))/365 which
doesn't work.

To fix your problem, format the cell with the DATEDIF formula as
General, or another number format.

OTOH, this would be a bit simpler:

=(A4-A3)/365

(formatted as a date), or, perhaps a bit more accurately if you're
looking for a result in years:

=DATEDIF(A3,A4,"y")

(3) Why doesn't DATEDIF appear in the Insert>Function... menu?

MS has never said, but I suspect it's a licensing issue. DATEDIF has
never appeared in XL Help, except for WinXL2000.

See

http://cpearson.com/excel/datedif.htm

for more info on DATEDIF.

 

[Brad Yundt]

1) Excel is behaving normally. It is trying to guess the
format of the cell, and incorrectly guessed "date"
because the DATEDIF function uses dates as inputs.
2) Try formatting the cell as a number rather than date.
It should then display the number of 365 day years
between your two dates.
3) See http://j-walk.com/ss/excel/odd/odd03.htm for a
little discussion on why DATEDIF isn't documented, but is
nevertheless usable