# Date-related problems - max and datevalue

1. ## 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 =----------------

2. ## Re: Date-related problems - max and datevalue

Works by me!

Cheers,
--
AP

"Dimitri Ulyinov" <icantbelieveitsnotmargerine@hotmail.com> a écrit dans le
message de news: kqieg.109891\$p_1.45425@fe03.news.easynews.com...
> 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 =----------------
>

3. ## 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" <icantbelieveitsnotmargerine@hotmail.com> wrote in message
news:kqieg.109891\$p_1.45425@fe03.news.easynews.com...
> 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 =----------------
>

4. 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

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

#### 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