It's not easy...
Let's start: assuming that in cell A1 we have : 15,332, the value of an angle formatted as decimal.
To convert this in DMS, we can do this by converting this value:
to find degrees:
=INT($A$1)
to find minutes:
=INT(($A$1-INT($A$1))*60)
to find seconds:
=(($A$1-INT($A$1))*60-INT(($A$1-INT($A$1))*60))*60
We can combine this results to show a piece of text formatted as DMS:
=INT($A$1)&"°"&INT(($A$1-INT($A$1))*60)&"'"&ROUND((($A$1-INT($A$1))*60-INT(($A$1-INT($A$1))*60))*60;2)&""""
To convert back this result 15°19'55,2" back to decimal value, this is another story...
to convert degrees:
=--(LEFT(A7; FIND("°";A7;1 ) - 1))
to convert minutes:
=--(MID(A7;FIND("°";A7;1)+1;FIND("'";A7;1)-FIND("°";A7;1)-1))/60
to convert seconds:
=--(MID(A7;FIND("'";A7;1)+1;LEN(A7)-FIND("'";A7;1)-1))/3600
Then, if you sum this 3 results, you will have the starting value in decimal value: 15,332:
=--(LEFT(A7; FIND("°";A7;1 ) - 1))+--(MID(A7;FIND("°";A7;1)+1;FIND("'";A7;1)-FIND("°";A7;1)-1))/60+--(MID(A7;FIND("'";A7;1)+1;LEN(A7)-FIND("'";A7;1)-1))/3600
You can use microsoft UDF, to do this:
Code:
Function Convert_Degree(Decimal_Deg) As Variant
With Application
'Set degree to Integer of Argument Passed
degrees = Int(Decimal_Deg)
'Set minutes to 60 times the number to the right
'of the decimal for the variable Decimal_Deg
minutes = (Decimal_Deg - degrees) * 60
'Set seconds to 60 times the number to the right of the
'decimal for the variable Minute
seconds = Format(((minutes - Int(minutes)) * 60), "0")
'Returns the Result of degree conversion
'(for example, 10.46 = 10~ 27 ' 36")
Convert_Degree = " " & degrees & "° " & Int(minutes) & "' " _
& seconds + Chr(34)
End With
End Function
Function Convert_Decimal(Degree_Deg As String) As Double
' Declare the variables to be double precision floating-point.
Dim degrees As Double
Dim minutes As Double
Dim seconds As Double
' Set degree to value before "°" of Argument Passed.
degrees = Val(Left(Degree_Deg, InStr(1, Degree_Deg, "°") - 1))
' Set minutes to the value between the "°" and the "'"
' of the text string for the variable Degree_Deg divided by
' 60. The Val function converts the text string to a number.
minutes = Val(Mid(Degree_Deg, InStr(1, Degree_Deg, "°") + 2, _
InStr(1, Degree_Deg, "'") - InStr(1, Degree_Deg, _
"°") - 2)) / 60
' Set seconds to the number to the right of "'" that is
' converted to a value and then divided by 3600.
seconds = Val(Mid(Degree_Deg, InStr(1, Degree_Deg, "'") + _
2, Len(Degree_Deg) - InStr(1, Degree_Deg, "'") - 2)) _
/ 3600
Convert_Decimal = degrees + minutes + seconds
End Function
but you shoud know that it has an error, the function will return 15°19'55" instead of 15°19'55,2", which is the precise conversion, and the reverse process is 15,33194 instead of 15,332
To solve this error in UDF, i would change the seconds determination in Convert_Degree to:
Code:
seconds = Format(((minutes - Int(minutes)) * 60), "#,#0.0")
and in Convert_Decimal:
Code:
seconds = Application.Substitute(Right(Degree_Deg, Len(Degree_Deg) - _
Application.Find("'", Degree_Deg, 1)), """", "") / 3600
or:
Code:
seconds = (Mid(Degree_Deg, InStr(1, Degree_Deg, "'") + 1, _
Len(Degree_Deg) - InStr(1, Degree_Deg, "'") - 1)) / 3600
With all this in mind (
), you can play with your needs...