Function for converting to minutes in decimal?

  • Thread starter Thread starter Masahiro Ito
  • Start date Start date
M

Masahiro Ito

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
 
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
 
Jody Gelowitz said:
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)
Click to expand...

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
 
Masahiro Ito said:
My code looks much more complicated than your formula, but I can't seem
to make your formula work?
Click to expand...

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
 
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
Click to expand...

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)
 
Tom Leylan said:
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)
Click to expand...

Very very thank you! perfect code - much better than mine.
 
Back
Top