i want to convert decimals into time format

[J.]

I have a (time) table with numbers such as:

1.28 , 0.15, etc...where the first digit represents the amount of minutes
and the two decimals represent seconds.

in the example: 1.28 = 1 minute and 28 seconds and I would like to convert
in excel
these tabulated times into a 00:00:00 format. So the outcome I'm looking for
in the conversion cell would be (for 1.28) 00:01:28 ; for 0.15 = 00:00:15 and
so on

could anyone help me?? and if you'd be so kind, could you explain the
formula to me so I can get the logic? thanks!!

 

[Bob Phillips]

Try

=--SUBSTITUTE("00:"&A25,".",":")

 

[trip_to_tokyo]

EXCEL 2007

1. In cell H13 (for example) type:-

14:00:00

2. Format cell H15 as General (right click / Format Cells . . . / Number tab
/ General)

3. Now in cell H15 type:-

=H13

This should return 0.583333

Not 100% sure if the above is what you want but if it is please hit Yes.

Thanks.

 

[PBezucha]

Bob,

The following VBA function helps me to do the described job:

Function TimeFrom(SeparatedRecord As Variant, _
Optional SecondsFraction) As Variant
'Function ensures manual input of larger time data series by using
'solely numeric keyboard, while respecting actual decimal separator.
'It must be written commenced with double minus> =--TimeFrom(…)
'Examples (dot separator):
'1.25 -> 1:25:00
'1..25 -> 1:25:00
'1.2.25 -> 1:02:25
'1..2..25 -> 1:02:25
'0.1.25 -> 0:01:25
'1..2.25 -> 0:01:02.25
'1..2..25.23 -> 1:02:25.23
Dim DecSeparator As String * 1, DoublePoint As String * 2
DecSeparator = Application.DecimalSeparator
DoublePoint = DecSeparator & DecSeparator
If Not IsMissing(SecondsFraction) Or _
InStr(1, SeparatedRecord, DoublePoint) > 0 Then
TimeFrom = WorksheetFunction.Substitute(SeparatedRecord, _
DoublePoint, ":")
Else
TimeFrom = WorksheetFunction.Substitute(SeparatedRecord, _
DecSeparator, ":")
End If
End Function

This conversion is fairly general; nevertheless the drawback is in ugly
output format that you would gain as well in your Excel function without
preceding double negation
=--Substitute(…)
Calling the function with it obviously does the trick. But to be smart, it
would be better to perform inside VBA procedure, especially if this should be
a part of class module. Unfortunately VBA doesn’t like double negation. Could
you possibly know a VBA equivalent of --? It should not be simple
formatting, as it would mean a loss of generality.

Sincerely

 

[Bob Phillips]

You could use this

Function TimeFrom(SeparatedRecord As Variant, _
Optional SecondsFraction) As Variant
'Function ensures manual input of larger time data series by using
'solely numeric keyboard, while respecting actual decimal separator.
'It must be written commenced with double minus> =--TimeFrom(.)
'Examples (dot separator):
'1.25 -> 1:25:00
'1..25 -> 1:25:00
'1.2.25 -> 1:02:25
'1..2..25 -> 1:02:25
'0.1.25 -> 0:01:25
'1..2.25 -> 0:01:02.25
'1..2..25.23 -> 1:02:25.23
Dim DecSeparator As String * 1, DoublePoint As String * 2
DecSeparator = Application.DecimalSeparator
DoublePoint = DecSeparator & DecSeparator
If Not IsMissing(SecondsFraction) Or _
InStr(1, SeparatedRecord, DoublePoint) > 0 Then
TimeFrom = WorksheetFunction.Substitute(SeparatedRecord, _
DoublePoint, ":")
Else
TimeFrom = WorksheetFunction.Substitute(SeparatedRecord, _
DecSeparator, ":")
End If
TimeFrom = CDate(TimeFrom)
End Function


but you would have to format the cell as well

 

[PBezucha]

Yes, exactly what I was after. Thanks, Bob.