LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #2   Report Post  
Posted to microsoft.public.excel.misc
Ardus Petus
 
Posts: n/a
Default 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" a écrit dans le
message de news: ...
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/ =-



 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 12:52 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"