435,551 Members | 2,622 Online
Need help? Post your question and get tips & solutions from a community of 435,551 IT Pros & Developers. It's quick & easy.

# Function for converting to minutes in decimal?

 P: n/a I have column of data that have elapsed time in minutes. For example: 1:15 1:47 23:12 I like to be able to use sql server to do simple math functions. Does anyone have a simple function/formula that will convert into a decimal minutes form? EG: 1:30 should equal 1.5 minutes Thanking you. Masa Nov 20 '05 #1
5 Replies

 P: n/a Once you have the Hours (whole number part of the value), Minutes, and Seconds (if you are using Seconds as well), the formula is: Value = Hours +( (Minutes + Seconds/60)/60) If you are not using Seconds, then you would use: Value = Hours + (Minutes/60) Jody "Masahiro Ito" wrote in message news:Xn************************@216.196.105.130... I have column of data that have elapsed time in minutes. For example: 1:15 1:47 23:12 I like to be able to use sql server to do simple math functions. Does anyone have a simple function/formula that will convert into a decimal minutes form? EG: 1:30 should equal 1.5 minutes Thanking you. Masa Nov 20 '05 #2

 P: n/a "Jody Gelowitz" wrote in news: Once you have the Hours (whole number part of the value), Minutes, and Seconds (if you are using Seconds as well), the formula is: Value = Hours +( (Minutes + Seconds/60)/60) If you are not using Seconds, then you would use: Value = Hours + (Minutes/60) Thank you Jody. I am trying this formula, but have trouble with it. my numbers come from a text file like: 1:15, 1:45, 10:37, 123:14. They are always minutes, then seconds. My code looks much more complicated than your formula, but I can't seem to make your formula work? Here is my code. I think I got it working, but it runs quite slow - I do this to millions of numbers. Public Function Time2Decimal(ByVal s As String) Dim i As Integer Dim dbl As Double Dim str() As String = s.Split(":") If str(1) = "00" Then dbl = CType(str(0), Double) Else Dim dbl2 As Double = CType(str(1), Double) dbl2 = dbl2 / 60 str(1) = dbl2.ToString Dim split() As String = str(1).Split(".") If split(1).Length > 2 Then split(1) = split(1).Remove(2, split(1).Length - 2) End If Dim sFin As String = str(0) & "." & split(1) dbl = CType(sFin, Double) End If Return dbl End Function Masa Nov 20 '05 #3

 P: n/a "Masahiro Ito" wrote... My code looks much more complicated than your formula, but I can't seem to make your formula work? This should work: Private Function TimeToMinutes(ByVal s As String) As Double Dim strs() As String = s.Split(":"c) MinSeconds = CDbl(strs(0)) + (CDbl(strs(1)) / 60) End Function Test it with this: Console.WriteLine(TimeToMinutes("1:15").ToString) Console.WriteLine(TimeToMinutes("2:45").ToString) Console.WriteLine(TimeToMinutes("30:37").ToString) Console.WriteLine(TimeToMinutes("123:14").ToString ) By the way... if it is possible that the string could be "0" or even "15" with no ":" separator it is a good idea to avoid adding additional code in the algorithm to handle it. Rather conform the data right away and use the common routine. So for instance if you do need to check for zero or a single value (you have to decide if a single value represents minutes or seconds) then check for the separator and if there isn't one then just add it. Pass this along to the converter. If you need to you can place those lines of code in TimeToMinutes but they should appear first... not interspersed inside. Tom Nov 20 '05 #4

 P: n/a "Tom Leylan" wrote... Oh wouldn't you know... I changed the function name as I posted the code and missed the return value... Private Function TimeToMinutes(ByVal s As String) As Double Dim strs() As String = s.Split(":"c) MinSeconds = CDbl(strs(0)) + (CDbl(strs(1)) / 60) End Function Here it is again with the lines that will handle "0" or "15" (assuming any value without the ":" separator is seconds. It is easy to make it assume minutes if you need that instead. Private Function TimeToMinutes(ByVal s As String) As Double If s.IndexOf(":"c) < 0 Then s = "0:" & s End If Dim strs() As String = s.Split(":"c) Return Convert.ToDouble(strs(0)) + (Convert.ToDouble(strs(1)) / 60) End Function Console.WriteLine(TimeToMinutes("0").ToString) Console.WriteLine(TimeToMinutes("15").ToString) Console.WriteLine(TimeToMinutes("1:15").ToString) Console.WriteLine(TimeToMinutes("2:45").ToString) Console.WriteLine(TimeToMinutes("30:37").ToString) Console.WriteLine(TimeToMinutes("401:20").ToString ) Nov 20 '05 #5

 P: n/a "Tom Leylan" wrote in Here it is again with the lines that will handle "0" or "15" (assuming any value without the ":" separator is seconds. It is easy to make it assume minutes if you need that instead. Private Function TimeToMinutes(ByVal s As String) As Double If s.IndexOf(":"c) < 0 Then s = "0:" & s End If Dim strs() As String = s.Split(":"c) Return Convert.ToDouble(strs(0)) + (Convert.ToDouble(strs(1)) / 60) Very very thank you! perfect code - much better than mine. Nov 20 '05 #6

### This discussion thread is closed

Replies have been disabled for this discussion.