I have a spreadsheet that contains general text: 1.05, 2.03, 0.03
This is really time (i.e., 1.05 equals 0:01:05). How do I convert this
general text to time values?

Try one of solutions below:
Your data in column A; the excel formula under column B; the Time value
shown under column C. Apply the formula.
Hope this helps.
Thanks

Your data Excel formula Time valu
1.05 =TIMEVALUE(""&LEFT($A2,FIND(".",$A2)-1)&":"&MID($A2,FIND(".",$A2)+1,2)&":00") 1:05:00 A
2.03 =TIMEVALUE(""&LEFT($A3,FIND(".",$A3)-1)&":"&MID($A3,FIND(".",$A3)+1,2)&":00") 2:03:00 A
0.03 =TIMEVALUE(""&LEFT($A4,FIND(".",$A4)-1)&":"&MID($A4,FIND(".",$A4)+1,2)&":00") 12:03:00 A
16.06.49 =TIMEVALUE(""&LEFT($A5,FIND(".",$A5)-1)&":"&MID($A5,FIND(".",$A5)+1,2)&":00") 4:06:00 P
16.06 =TIMEVALUE(""&LEFT($A6,FIND(".",$A6)-1)&":"&MID($A6,FIND(".",$A6)+1,2)&":00") 4:06:00 PM


"cs_vision" wrote:

I have a spreadsheet that contains general text: 1.05, 2.03, 0.03
This is really time (i.e., 1.05 equals 0:01:05). How do I convert this
general text to time values?

I forgot to tell you the 1.05, 2.03, 03, etc it actually 1 minute and 5 secs,
so it should look like 0:01:05; 0:02:03; 0:00:03. What do I need to change
in the formula?

"Rudy Escoto" wrote:

Try one of solutions below:
Your data in column A; the excel formula under column B; the Time value
shown under column C. Apply the formula.
Hope this helps.
Thanks

Your data Excel formula Time value
1.05 =TIMEVALUE(""&LEFT($A2,FIND(".",$A2)-1)&":"&MID($A2,FIND(".",$A2)+1,2)&":00") 1:05:00 AM
2.03 =TIMEVALUE(""&LEFT($A3,FIND(".",$A3)-1)&":"&MID($A3,FIND(".",$A3)+1,2)&":00") 2:03:00 AM
0.03 =TIMEVALUE(""&LEFT($A4,FIND(".",$A4)-1)&":"&MID($A4,FIND(".",$A4)+1,2)&":00") 12:03:00 AM
16.06.49 =TIMEVALUE(""&LEFT($A5,FIND(".",$A5)-1)&":"&MID($A5,FIND(".",$A5)+1,2)&":00") 4:06:00 PM
16.06 =TIMEVALUE(""&LEFT($A6,FIND(".",$A6)-1)&":"&MID($A6,FIND(".",$A6)+1,2)&":00") 4:06:00 PM


"cs_vision" wrote:

I have a spreadsheet that contains general text: 1.05, 2.03, 0.03
This is really time (i.e., 1.05 equals 0:01:05). How do I convert this
general text to time values?

You need not have to change the formulas. Simply format the cells in the time
value results by Format | Cells... | Custom | Type as "[$-409]h:mm:ss;@".
Thanks
Rudy Escoto

"cs_vision" wrote:

I forgot to tell you the 1.05, 2.03, 03, etc it actually 1 minute and 5 secs,
so it should look like 0:01:05; 0:02:03; 0:00:03. What do I need to change
in the formula?

"Rudy Escoto" wrote:

Try one of solutions below:
Your data in column A; the excel formula under column B; the Time value
shown under column C. Apply the formula.
Hope this helps.
Thanks

Your data Excel formula Time value
1.05 =TIMEVALUE(""&LEFT($A2,FIND(".",$A2)-1)&":"&MID($A2,FIND(".",$A2)+1,2)&":00") 1:05:00 AM
2.03 =TIMEVALUE(""&LEFT($A3,FIND(".",$A3)-1)&":"&MID($A3,FIND(".",$A3)+1,2)&":00") 2:03:00 AM
0.03 =TIMEVALUE(""&LEFT($A4,FIND(".",$A4)-1)&":"&MID($A4,FIND(".",$A4)+1,2)&":00") 12:03:00 AM
16.06.49 =TIMEVALUE(""&LEFT($A5,FIND(".",$A5)-1)&":"&MID($A5,FIND(".",$A5)+1,2)&":00") 4:06:00 PM
16.06 =TIMEVALUE(""&LEFT($A6,FIND(".",$A6)-1)&":"&MID($A6,FIND(".",$A6)+1,2)&":00") 4:06:00 PM


"cs_vision" wrote:

I have a spreadsheet that contains general text: 1.05, 2.03, 0.03
This is really time (i.e., 1.05 equals 0:01:05). How do I convert this
general text to time values?

Excel stores time as fractions of a day (24hrs =1). So 12 hours=12/24, 1
hrs=1/24, 1 min=1/(24*60), 1 sec=1/(24*60*60).
So you need to
- separate the minutes, "=TRUNC("1.03"),
- multiply this by the value of 1min,
- separate the seconds, "="MOD("1.03",1),
- multiply this by 100, then by the value of 1sec.
- add the 2 numbers
This gives you a decimal number.

How Excel displays this is then a matter of the applied format. It looks
like you want "hh:mm:ss"
Note that if you format like this, minute values greater than 60 will
display as hours, e.g. 01:05:23, not 65:23.

NickHK

"cs_vision" wrote in message
...
I forgot to tell you the 1.05, 2.03, 03, etc it actually 1 minute and 5
secs,
so it should look like 0:01:05; 0:02:03; 0:00:03. What do I need to
change
in the formula?

"Rudy Escoto" wrote:

Try one of solutions below:
Your data in column A; the excel formula under column B; the Time value
shown under column C. Apply the formula.
Hope this helps.
Thanks

Your data Excel formula Time value
1.05
=TIMEVALUE(""&LEFT($A2,FIND(".",$A2)-1)&":"&MID($A2,FIND(".",$A2)+1,2)&":00"
) 1:05:00 AM
2.03
=TIMEVALUE(""&LEFT($A3,FIND(".",$A3)-1)&":"&MID($A3,FIND(".",$A3)+1,2)&":00"
) 2:03:00 AM
0.03
=TIMEVALUE(""&LEFT($A4,FIND(".",$A4)-1)&":"&MID($A4,FIND(".",$A4)+1,2)&":00"
) 12:03:00 AM
16.06.49
=TIMEVALUE(""&LEFT($A5,FIND(".",$A5)-1)&":"&MID($A5,FIND(".",$A5)+1,2)&":00"
) 4:06:00 PM
16.06
=TIMEVALUE(""&LEFT($A6,FIND(".",$A6)-1)&":"&MID($A6,FIND(".",$A6)+1,2)&":00"
) 4:06:00 PM


"cs_vision" wrote:

I have a spreadsheet that contains general text: 1.05, 2.03, 0.03
This is really time (i.e., 1.05 equals 0:01:05). How do I convert
this
general text to time values?