+ Reply to Thread
Results 1 to 4 of 4

DateDif problem

  1. #1
    Annie
    Guest

    DateDif problem

    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?

  2. #2
    Gord Dibben
    Guest

    Re: DateDif problem

    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

    On Wed, 16 Nov 2005 14:03:30 -0800, "Elkar" <[email protected]>
    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?



  3. #3
    Annie
    Guest

    Re: DateDif problem

    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 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
    >
    > On Wed, 16 Nov 2005 14:03:30 -0800, "Elkar" <[email protected]>
    > 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?

    >
    >


  4. #4
    Ron Rosenfeld
    Guest

    Re: DateDif problem

    On Thu, 17 Nov 2005 17:52:58 -0800, "Annie" <[email protected]>
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1