Finding the oldest date in a column
 

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

Finding the oldest date in a column
 

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

On Nov 12, 11:15 pm, "Robert Smith"
wrote:
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

Finding the oldest date in a column
 

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

"Pete_UK" wrote in message
ups.com...
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

On Nov 12, 11:15 pm, "Robert Smith"
wrote:
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

Finding the oldest date in a column
 

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

On Nov 12, 11:54 pm, "Robert Smith"
wrote:
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

"Pete_UK" wrote in message

ups.com...
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

On Nov 12, 11:15 pm, "Robert Smith"
wrote:



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- Hide quoted text -

- Show quoted text -

Finding the oldest date in a column
 

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

"Pete_UK" wrote in message
ups.com...
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

On Nov 12, 11:54 pm, "Robert Smith"
wrote:
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

"Pete_UK" wrote in message

ups.com...
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

On Nov 12, 11:15 pm, "Robert Smith"
wrote:



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- Hide quoted text -

- Show quoted text -

Finding the oldest date in a column
 

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

Thanks a bunch

Bob
"Robert Smith" wrote in message
...
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

"Pete_UK" wrote in message
ups.com...
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

On Nov 12, 11:54 pm, "Robert Smith"
wrote:
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

"Pete_UK" wrote in message

ups.com...
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

On Nov 12, 11:15 pm, "Robert Smith"
wrote:



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- Hide quoted text -

- Show quoted text -

Finding the oldest date in a column
 

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

Pete

On Nov 13, 12:47 am, "Robert Smith"
wrote:
Yo Pete, your array formula does work. Turns out I copied an extra space at
the end of your formula.

Thanks a bunch

Bob"Robert Smith" wrote in message

...



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

"Pete_UK" wrote in message
oups.com...
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

On Nov 12, 11:54 pm, "Robert Smith"
wrote:
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

"Pete_UK" wrote in message

roups.com...
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

On Nov 12, 11:15 pm, "Robert Smith"
wrote:

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -