DateDif problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 Data>Text to Columns>Next>Next>Column Data Format>Date.

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


Gord Dibben Excel MVP
 
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.
 
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.
Click to expand...

Tools/Options/Transition and DEselect Transition Formula Evaluation


--ron
 
Back
Top