LOOKUP and Time Calc
 

I am trying to find the last value in a column, then convert that value
(stored as "General") to seconds in the new sheet.

I've been trying:

=--(TEXT((LOOKUP(99^99,'[HBS wed.
USER.xls]Sheet1'!$F:$F)),"00\:00\:00")*86400)

but get a #N/A error.

Any help?
TIA
Greg

LOOKUP and Time Calc
 

What does the data look like that your are looking up?
Your TEXT function is taking a normal number like 123.45
and converting it's appearance to:
00\:01\:23
Note that this isn't being stored as a time, its just the same number with
some symbols thrown in.
If you wanted time, use TEXT format of
"hh/:mm/:ss"
Or, if your data is actually text, you may need to use the TIMEVALUE function.

Again, if we knew what your data looked like, we'd be able to offer better
assistance.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Greg" wrote:

I am trying to find the last value in a column, then convert that value
(stored as "General") to seconds in the new sheet.

I've been trying:

=--(TEXT((LOOKUP(99^99,'[HBS wed.
USER.xls]Sheet1'!$F:$F)),"00\:00\:00")*86400)

but get a #N/A error.

Any help?
TIA
Greg

LOOKUP and Time Calc
 

Try it like this:

=LOOKUP(99^99,'[HBS wed.USER.xls]Sheet1'!$F:$F)*86400

I assume that the other file is open, otherwise you will need to
include the full path to the file. I also assume that the data you are
retrieving is recognisable as a time value.

Hope this helps.

Pete

On Jul 24, 3:24*pm, Greg wrote:
I am trying to find the last value in a column, then convert that value
(stored as "General") to seconds in the new sheet.

I've been trying:

=--(TEXT((LOOKUP(99^99,'[HBS wed.
USER.xls]Sheet1'!$F:$F)),"00\:00\:00")*86400)

but get a #N/A error.

Any help?
TIA
Greg

LOOKUP and Time Calc
 

Sorry Luke, I should have provided that up front.

00:04:06 formatted as "General"

Thank you for your help.

Greg

"Luke M" wrote:

What does the data look like that your are looking up?
Your TEXT function is taking a normal number like 123.45
and converting it's appearance to:
00\:01\:23
Note that this isn't being stored as a time, its just the same number with
some symbols thrown in.
If you wanted time, use TEXT format of
"hh/:mm/:ss"
Or, if your data is actually text, you may need to use the TIMEVALUE function.

Again, if we knew what your data looked like, we'd be able to offer better
assistance.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Greg" wrote:

I am trying to find the last value in a column, then convert that value
(stored as "General") to seconds in the new sheet.

I've been trying:

=--(TEXT((LOOKUP(99^99,'[HBS wed.
USER.xls]Sheet1'!$F:$F)),"00\:00\:00")*86400)

but get a #N/A error.

Any help?
TIA
Greg

LOOKUP and Time Calc
 

Thanks Pete, but that's what I tried first, then I was reading on the forum
about forcing general to numbers which is why I tried the posted formula.

I appreciate your effort!

Thanks,

Greg

"Pete_UK" wrote:

Try it like this:

=LOOKUP(99^99,'[HBS wed.USER.xls]Sheet1'!$F:$F)*86400

I assume that the other file is open, otherwise you will need to
include the full path to the file. I also assume that the data you are
retrieving is recognisable as a time value.

Hope this helps.

Pete

On Jul 24, 3:24 pm, Greg wrote:
I am trying to find the last value in a column, then convert that value
(stored as "General") to seconds in the new sheet.

I've been trying:

=--(TEXT((LOOKUP(99^99,'[HBS wed.
USER.xls]Sheet1'!$F:$F)),"00\:00\:00")*86400)

but get a #N/A error.

Any help?
TIA
Greg