If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
i want to convert decimals into time format
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!! |
#2
|
|||
|
|||
i want to convert decimals into time format
Try
=--SUBSTITUTE("00:"&A25,".",":") -- HTH Bob "J." wrote in message ... 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!! |
#3
|
|||
|
|||
i want to convert decimals into time format
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. "J." wrote: 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!! |
#4
|
|||
|
|||
i want to convert decimals into time format
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 -- Petr Bezucha "Bob Phillips" wrote: Try =--SUBSTITUTE("00:"&A25,".",":") -- HTH Bob "J." wrote in message ... 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!! . |
#5
|
|||
|
|||
i want to convert decimals into time format
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 -- HTH Bob "PBezucha" wrote in message ... 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 -- Petr Bezucha "Bob Phillips" wrote: Try =--SUBSTITUTE("00:"&A25,".",":") -- HTH Bob "J." wrote in message ... 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!! . |
#6
|
|||
|
|||
i want to convert decimals into time format
Yes, exactly what I was after. Thanks, Bob.
-- Petr Bezucha |
Thread Tools | |
Display Modes | |
|
|