RDE wrote:
Hylton Conacher (ZR1HPC) wrote:
If your text is in cell A1, you could try using the SUBSTITUTE function three times with the TIMEVALUE function like this:
B1=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"h ";":");"m ";":");"s";""))
mmm, Unfortunately there are no colons in the string provided by kppp logviewer, only spaces.
Exactly. That's what the SUBSTITUTE function does here. It looks for the "h " and "m " and replaces them with the ":". It also just gets rid of the final "s" and replaces it with nothing.
This converts a string like "10h 14m 36s" into "10:14:36". It also converts "14m 36s" into "14:36". I see said the blind man.
and
C1=IF(LEN(B1)>5;TIMEVALUE(B1);TIMEVALUE("00:" & B1))
What this next function does depends on whether the value of B1 calculated from the SUBSTITUTE function above returns "hh:mm:ss" or "mm:ss". If the former it just uses TIMEVALUE() to give you the decimal fraction of a day. Otherwise it concatenates "00:" in front of the "mm:ss" to give your "00:mm:ss" before it feeds it to the TIMEVALUE() function.
As you can surmise, if you need to take care of the case of just seconds and no minutes, then you either need to nest another IF() statement in there or perhaps rewrite it as a CASE() function. I sorta see that another one needs to be nested but DO NOT ask me to figure it out, :)
TIMEVALUE help says that the time string must be in a certain format and I see the format used by kppp Log viewer is not supported.... and yet your idea would probably work.
That's the whole point. It uses some string functions and IF logic to convert the "##h ##m ##s" format to "##:##:##" for the TIMEVALUE() function. Doh! on my part.
If the log can give you a string that's just "zzs", then you need to adjust the C1 formula.
It is most definitely possible but I wouldn't know where to start editing C1.
You can probably cram it all into one cell, but it would be a hell of a thing. Damn right, and God help me if I need it to reference a different set of cells :)
How would you combine the formulae in blocks B1 and C1 so that it could be entered once ie data is in A1, answer must be in A2.
Well, you're just nesting the formulas. You would probably have a lot easier time doing this using the formula autopilot. It will handle the nesting for you and make sure you have all the closing parenthesis in the right places. From the experiences I have had with Autopilot, I would say it is aimed at simpler formulae.
The end result will look like hell, but it should work. Once you have it in one cell, just copy and paste as needed.
If I get a little time later today, I'll see what I can do and then post the result. I would definitely appreciate it.
Regards Hylton -- ======================================================================== Hylton Conacher - Linux user # 229959 at http://counter.li.org Currently using SuSE 9.0 Professional with KDE 3.1 Licenced Windows user ========================================================================