When we open a Lotus file in Excel all the data converted properly. Now when
entering a DATEDIF function in a cell it returns a zero (0) answer on any of
the three sheets that were part of the original Lotus file. If we insert a
new sheet DateDif works. If we open an new blank file DateDif works. We
have tired reformatting the cells to numeric and many other formats but
DateDif will not work in the three original sheets of the file. WHy?

Elkar is likely correct that the dates are stored as text.

Format to General. Do they turn into serial numbers?

If not, they are text.

Simple re-formatting will not do the trick.

Try DataText to ColumnsNextNextColumn Data FormatDate.

Pick MDY or DMY or whatever turns them to real dates.


Gord Dibben Excel MVP

On Wed, 16 Nov 2005 14:03:30 -0800, "Elkar"
wrote:

This is just a guess, but are the dates stored as text? If you look in the
formula bar, is there an apostrophe in front of the date? The apostrophe
will override any cell formatting you may have.

"Annie" wrote:

When we open a Lotus file in Excel all the data converted properly. Now when
entering a DATEDIF function in a cell it returns a zero (0) answer on any of
the three sheets that were part of the original Lotus file. If we insert a
new sheet DateDif works. If we open an new blank file DateDif works. We
have tired reformatting the cells to numeric and many other formats but
DateDif will not work in the three original sheets of the file. WHy?

I am not referencing dates in a cell. The Datedif syntax is:
=datedif("2001/1/1","2003/4/2","d")

It returns 0 on the converted sheets but on new sheets or new files it
returns 821.

I did get an email from Microsoft and they are aware that datedif won't work
on converted files but are not supporting excel 2000 and will not provide a
solution other than to upgrade our software. Not possible, it is a very
large corporation and that is out of my hands.


"Gord Dibben" wrote:

Elkar is likely correct that the dates are stored as text.

Format to General. Do they turn into serial numbers?

If not, they are text.

Simple re-formatting will not do the trick.

Try DataText to ColumnsNextNextColumn Data FormatDate.

Pick MDY or DMY or whatever turns them to real dates.


Gord Dibben Excel MVP

On Wed, 16 Nov 2005 14:03:30 -0800, "Elkar"
wrote:

This is just a guess, but are the dates stored as text? If you look in the
formula bar, is there an apostrophe in front of the date? The apostrophe
will override any cell formatting you may have.

"Annie" wrote:

When we open a Lotus file in Excel all the data converted properly. Now when
entering a DATEDIF function in a cell it returns a zero (0) answer on any of
the three sheets that were part of the original Lotus file. If we insert a
new sheet DateDif works. If we open an new blank file DateDif works. We
have tired reformatting the cells to numeric and many other formats but
DateDif will not work in the three original sheets of the file. WHy?

On Thu, 17 Nov 2005 17:52:58 -0800, "Annie"
wrote:

I am not referencing dates in a cell. The Datedif syntax is:
=datedif("2001/1/1","2003/4/2","d")

It returns 0 on the converted sheets but on new sheets or new files it
returns 821.

Tools/Options/Transition and DEselect Transition Formula Evaluation


--ron