LOOKUP and Time Calc

[Greg]

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

 

[Luke M]

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.

 

[Pete_UK]

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

 

[Greg]

Sorry Luke, I should have provided that up front.

00:04:06 formatted as "General"

Thank you for your help.

Greg

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!*

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

 

[Greg]

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

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

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