LOOKUP and Time Calc

  • Thread starter Thread starter Greg
  • Start date Start date
G

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
 
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.
 
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
 
Sorry Luke, I should have provided that up front.

00:04:06 formatted as "General"

Thank you for your help.

Greg

Luke M said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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
Click to expand...
Click to expand...
 
Back
Top