Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calc time | Excel Discussion (Misc queries) | |||
Time Calc 24 hrs | Excel Worksheet Functions | |||
Time Calc | Excel Worksheet Functions | |||
calc time value | Excel Discussion (Misc queries) | |||
time sheet calc by time in and out | Excel Worksheet Functions |