Time expressed in hours minutes and seconds
Posted by clive waters on October 31, 2001 11:28 AM
I would be glad of any help.
I get time expressed in a cell as
"134205" which means 13 hours, 42 minutes and 5 seconds all the way down to "234" which is 2 minutes 34 seconds. I tried a simple formula but the times are so variable I need a way please to change them more speedily than 1 at a time
Thanks
Posted by Barrie Davidson on October 31, 2001 11:41 AM
How about this formula.
=IF(LEN(A1)>4,VALUE(LEFT(A1,LEN(A1)-4)&":"&MID(A1,LEN(A1)-3,2)&":"&RIGHT(A1,2)),IF(LEN(A1)>2,VALUE("0:"&MID(A1,1,LEN(A1)-2)&":"&RIGHT(A1,2)),VALUE("0:"&"0:"&RIGHT(A1,2))))
You'll have to custom format the cell to make it readable ([h]:mm:ss).
Regards,
BarrieBarrie Davidson
Posted by Jerid on October 31, 2001 11:48 AM
Can you find a specific patteren in your values?
Example:
6 digits is always HHMMSS
5 digits is always HMMSS
4 digits is always MMSS
3 digits is always MSS
2 digits is always SS
1 digit is always S
Or is 3 digits somtimes MSS and sometimes HMM.
Posted by Jerid on October 31, 2001 11:57 AM
I would use Barrie's formula, if your data is consistent. (NT)
Posted by Aladin Akyurek on October 31, 2001 12:02 PM
In B1 enter: =TIME(LEFT(REPT(0,6-LEN(A1))&A1,2),MID(REPT(0,6-LEN(A1))&A1,3,2),RIGHT(REPT(0,6-LEN(A1))&A1,2))
where A1 houses the first entry to convert.
Format B1 as time and copy down the formula as far as needed.
Aladin
==========
Posted by Barrie Davidson on October 31, 2001 12:08 PM
Aladin, what happens if
Clive's data has something like 120 hours, 14 minutes, and 33 seconds? Your formula will only work if his data is six digits, no?
Regards,
Barrie
Posted by Barrie Davidson on October 31, 2001 12:09 PM
Correction, six digits or less (nt)
Posted by Aladin Akyurek on October 31, 2001 12:31 PM
Yep. (Re: Correction, six digits or less (nt))
I just assumed 6-digit representation implying ordinary time.
Posted by Mark W. on October 31, 2001 1:06 PM
Use =SUM(MID(TEXT(A1,"000000"),{1,3,5},2)/{24,1440,86400})
where your "time" is entered in cell A1. Format
the cell containing this formula as: [h]:mm:ss
Posted by Mark W. on October 31, 2001 1:17 PM
Re: Aladin, what happens if
When Clive says, "134205" which means 13 hours,
42 minutes and 5 seconds all the way down to
"234", I take that to mean that the upper limit
on number of chars is 6 and the lower limit is 3.
Take a look at my solution above!
Posted by Mark W. on October 31, 2001 1:40 PM
Making allowances for hours > 99, one could use...
Posted by Jerid on November 01, 2001 5:10 AM
Re: Making allowances for hours > 99, one could use...
It took me awhile, but I finally figured out your formulas. I'm a VB guy, and need to get more involved with Excel formulas.
I just have one question, what is the Max function giving you that the Len function isn't already returning?
MAX(0,LEN(A9)-4))
Len(A9)-4 returns 3 for 120 hours, 2 for 99 hours, 1 for 5 hours, and 0 to no hours.
Thanks for the great formulas.
Jerid
Posted by Mark W. on November 01, 2001 9:26 AM
Re: Making allowances for hours > 99, one could use...
MAX(0,...) insures that 2nd argument of LEFT()
isn't negative. If the length of the "time" value
is less than 4 (as is for "234") A9 will be
negative. LEFT(...,-1) will produce a #VALUE!
error. LEFT(...,0) will produce the empty string,
"". ""/24 will also produce a #VALUE! error. I
use 0&LEFT(...,0) to produce "0"/24 and avoid
this problem. You've probably already learned
that mathematical operators (i.e., /) will
coerce a text representation of a number
(i.e., "0") into a numeric value so "0"/24 won't
result in a #VALUE! error.
Posted by Mark W. on November 01, 2001 1:21 PM
Correction... Fire my editor!!! : )
If the length of the "time" value is less than 4
(as is for "234") A9 will be negative." should
read...
"If the length of the "time" value is less than 4
(as is for "234") LEN(A9)-4 will be negative."
