i want to convert decimals into time format
in [General]
|
From: J. on 11 May 2010 12:06 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!!
From: Bob Phillips on 11 May 2010 12:33 Try =--SUBSTITUTE("00:"&A25,".",":") -- HTH Bob "J." <J.(a)discussions.microsoft.com> wrote in message news:3AD925A2-8673-4AA8-9A9A-589F0D1BB783(a)microsoft.com... >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!!
From: trip_to_tokyo on 11 May 2010 12:46 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!!
From: PBezucha on 12 May 2010 10:32 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." <J.(a)discussions.microsoft.com> wrote in message > news:3AD925A2-8673-4AA8-9A9A-589F0D1BB783(a)microsoft.com... > >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!! > > > . >
From: Bob Phillips on 13 May 2010 03:38
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" <PBezucha(a)discussions.microsoft.com> wrote in message news:6C82A796-BD31-4B46-9340-BEABE3882BE5(a)microsoft.com... > 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." <J.(a)discussions.microsoft.com> wrote in message >> news:3AD925A2-8673-4AA8-9A9A-589F0D1BB783(a)microsoft.com... >> >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!! >> >> >> . >> |