Finding the oldest date in a column

[Robert Smith]

Hi folks, am doing web queries pulling down data. In column A, the date and time show up - Oct 4, 2007, 5:00 pm.


In any worksheet, there could be between 1 and 50 rows with data. The most recent date & time show up in cell A5 in each worksheet. I'd like to find the oldest date for each worksheet.

Thanks to another soul here from a prior inquiry, he said that I needed to convert the date, so in column J on each worksheet, I have the following formula for each row. =DATEVALUE(LEFT(A5,FIND(",",A5,FIND(",",A5,1)+1)-1))+TIMEVALUE(MID(A5,FIND(",",A5,FIND(",",A5,1)+1)+2,255)) .

I'd like to find the oldest date in either column A or Column J which could be in any row between row 5 or row 55.

Any help is appreciated.

Regards,

Bob

 

[Pete_UK]

If you have (guaranteed) date formats in column J, then this will
return the oldest date:

=MIN(J5:J55)

Format the cell as a date.

Hope this helps.

Pete

 

[Robert Smith]

Thanks for the timely reply Pete, but I'm getting a #VALUE error in the
cell, base on the fact that in the one worksheet I applied the formula, Row
28 and lower has no data in it. Like I said, there could be 1 row of data or
50 rows of data.

Now, I did check it on a worksheet full of 50s row of data, and it works
great. Any idea on how to pull the data down when the final cell to look at
is unknown?

Thanks,

Bob

If you have (guaranteed) date formats in column J, then this will
return the oldest date:

=MIN(J5:J55)

Format the cell as a date.

Hope this helps.

Pete

 

[Pete_UK]

Okay, try this array* formula:

=MIN(IF(OR(J5:J55="",ISERROR(J5:J55)),50000,J5:J55))

*As this is an array formula, then once you have typed it in (or
subsequently amend it), you must commit it with CTRL-SHIFT-ENTER
(CSE), rather than the usual ENTER. If you do this correctly then
Excel will wrap curly braces { } around the formula when viewed in the
formula bar - you must not type these yourself.

Hope this helps.

Pete

 

[Robert Smith]

Again, thanks for the timely reply Pete.

Hate to mention this, but I'm still getting the #VALUE error message, and
yes, I am entering Cntl-Shift-Enter.

By the by, it's actually 54, not 55.

Any additional advice you can provide would be appreciated.

Regards,

Bob

Okay, try this array* formula:

=MIN(IF(OR(J5:J55="",ISERROR(J5:J55)),50000,J5:J55))

*As this is an array formula, then once you have typed it in (or
subsequently amend it), you must commit it with CTRL-SHIFT-ENTER
(CSE), rather than the usual ENTER. If you do this correctly then
Excel will wrap curly braces { } around the formula when viewed in the
formula bar - you must not type these yourself.

Hope this helps.

Pete

 

[Robert Smith]

Yo Pete, your array formula does work. Turns out I copied an extra space at
the end of your formula.

Thanks a bunch

Bob

 

[Pete_UK]

Glad to hear it, Bob - thanks for feeding back.

Pete