+ Reply to Thread
Results 1 to 4 of 4

Date-related problems - max and datevalue

  1. #1
    Dimitri Ulyinov
    Guest

    Date-related problems - max and datevalue

    Hey everyone,

    I have been given approx. 20,000 rows worth of data, in 3 columns, all of them dates (being in Australia, in dd/mm/yy format) to work with in Excel 2002.

    For one of the columns, I have to benchmark it against 2 other dates which I have entered myself and formatted properly as dates.

    Problem 1 is when I try and find the latest of these 3 dates (1 given, 2 inputted by me). I have tried
    =MAX(I10,$L$1,$B10), where I10 = 1/3/2002 (given), $L$1 = 29/12/1998 (inputted and formatted properly by me), and $B10 = 12/12/1991 (also inputted by me)
    but it KEEPS returning 29/12/1998!!! (This is laughable, I opened the dialog box for this formula, and it has them all stored as numbers, and 1/3/2002 is the biggest, yet the answer is still 29/12/1998)

    Thinking that this is probably because the given dates haven't been formatted properly as dates, I've used the datevalue function to put them into a recognisable form, with some success. However...

    For one of the columns of given data, if a certain event occurred (usually death), it has a date attached to it, but if it didn't occur, no date is attached. However applying datevalue to these empty cells produces a #VALUE! error.

    What to do? Can I force Excel to recognise the given data as a proper date, ruling out the need for this date function business and so max works properly? Or if I have to apply date function to everything, how do I get it to return a meaningful value (say, zero), if it encounters an empty cell?

    Hope I've expressed myself clearly, and any help would be greatly appreciated,

    Regards,

    Dimitri Ulyinov,
    Sydney, Australia


    --------------= Posted using GrabIt =----------------
    ------= Binary Usenet downloading made easy =---------
    -= Get GrabIt for free from http://www.shemes.com/ =-


  2. #2
    Ardus Petus
    Guest

    Re: Date-related problems - max and datevalue

    Works by me!

    Could you upload your workbook on http://cjoint.com and post back the link?

    Cheers,
    --
    AP

    "Dimitri Ulyinov" <[email protected]> a écrit dans le
    message de news: [email protected]...
    > Hey everyone,
    >
    > I have been given approx. 20,000 rows worth of data, in 3 columns, all of
    > them dates (being in Australia, in dd/mm/yy format) to work with in Excel
    > 2002.
    >
    > For one of the columns, I have to benchmark it against 2 other dates which
    > I have entered myself and formatted properly as dates.
    >
    > Problem 1 is when I try and find the latest of these 3 dates (1 given, 2
    > inputted by me). I have tried
    > =MAX(I10,$L$1,$B10), where I10 = 1/3/2002 (given), $L$1 = 29/12/1998
    > (inputted and formatted properly by me), and $B10 = 12/12/1991 (also
    > inputted by me)
    > but it KEEPS returning 29/12/1998!!! (This is laughable, I opened the
    > dialog box for this formula, and it has them all stored as numbers, and
    > 1/3/2002 is the biggest, yet the answer is still 29/12/1998)
    >
    > Thinking that this is probably because the given dates haven't been
    > formatted properly as dates, I've used the datevalue function to put them
    > into a recognisable form, with some success. However...
    >
    > For one of the columns of given data, if a certain event occurred (usually
    > death), it has a date attached to it, but if it didn't occur, no date is
    > attached. However applying datevalue to these empty cells produces a
    > #VALUE! error.
    >
    > What to do? Can I force Excel to recognise the given data as a proper
    > date, ruling out the need for this date function business and so max works
    > properly? Or if I have to apply date function to everything, how do I get
    > it to return a meaningful value (say, zero), if it encounters an empty
    > cell?
    >
    > Hope I've expressed myself clearly, and any help would be greatly
    > appreciated,
    >
    > Regards,
    >
    > Dimitri Ulyinov,
    > Sydney, Australia
    >
    >
    > --------------= Posted using GrabIt =----------------
    > ------= Binary Usenet downloading made easy =---------
    > -= Get GrabIt for free from http://www.shemes.com/ =-
    >




  3. #3
    Ragdyer
    Guest

    Re: Date-related problems - max and datevalue

    Have you checked that all the sheets and data are using the same date
    *system*?
    <Tools> <Options> <Calculation> tab,
    Check *OR* uncheck,
    "1904 Date System"
    To match each other.
    --
    HTH,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "Dimitri Ulyinov" <[email protected]> wrote in message
    news:[email protected]...
    > Hey everyone,
    >
    > I have been given approx. 20,000 rows worth of data, in 3 columns, all of

    them dates (being in Australia, in dd/mm/yy format) to work with in Excel
    2002.
    >
    > For one of the columns, I have to benchmark it against 2 other dates which

    I have entered myself and formatted properly as dates.
    >
    > Problem 1 is when I try and find the latest of these 3 dates (1 given, 2

    inputted by me). I have tried
    > =MAX(I10,$L$1,$B10), where I10 = 1/3/2002 (given), $L$1 = 29/12/1998

    (inputted and formatted properly by me), and $B10 = 12/12/1991 (also
    inputted by me)
    > but it KEEPS returning 29/12/1998!!! (This is laughable, I opened the

    dialog box for this formula, and it has them all stored as numbers, and
    1/3/2002 is the biggest, yet the answer is still 29/12/1998)
    >
    > Thinking that this is probably because the given dates haven't been

    formatted properly as dates, I've used the datevalue function to put them
    into a recognisable form, with some success. However...
    >
    > For one of the columns of given data, if a certain event occurred (usually

    death), it has a date attached to it, but if it didn't occur, no date is
    attached. However applying datevalue to these empty cells produces a #VALUE!
    error.
    >
    > What to do? Can I force Excel to recognise the given data as a proper

    date, ruling out the need for this date function business and so max works
    properly? Or if I have to apply date function to everything, how do I get it
    to return a meaningful value (say, zero), if it encounters an empty cell?
    >
    > Hope I've expressed myself clearly, and any help would be greatly

    appreciated,
    >
    > Regards,
    >
    > Dimitri Ulyinov,
    > Sydney, Australia
    >
    >
    > --------------= Posted using GrabIt =----------------
    > ------= Binary Usenet downloading made easy =---------
    > -= Get GrabIt for free from http://www.shemes.com/ =-
    >



  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Sounds like your dates in column I are text formatted - try this

    select column I
    Data > Text to columns > Finish

    This should convert these to recognisable dates

+ 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