Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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/ =- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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" wrote in message ... 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date-related problems - max and datevalue
Sounds like your dates in column I are text formatted - try this select column I Data Text to columns Finish This should convet these to recogmisable dates -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=546274 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|