Here are two macros that do the job for my astronomy spreadsheet. The direction values (deg, min, sec) are formatted as text. If cell A1 contains -77° 26' 09.5"
, I can enter =DMS2Real(A1)
in cell A2, which will then display -77.4359722222
. =Real2DMS(A2)
in A3 will reverse the conversion.
Enter the macro functions via Tools > Macros > Organize Macros > LibreOffice Basic > Standard (or wherever you would like to store them).
Note that decimal degrees are not the radian values required by trig functions.
' Convert degrees, minutes, seconds to a single real number and back.
' Tested in LibreOffice Calc 5.1.6.2
' Based on https://support.microsoft.com/en-gb/help/213449/how-to-convert-degrees-minutes-seconds-angles-to-or-from-decimal-angle
Function DMS2Real(Degree_DMS As String) As Double ' -77° 26' 09.5" -> -77.4359722222
' use double precision floating-point.
Dim degrees As Double
Dim minutes As Double
Dim seconds As Double
Dim sign As Integer
' Set degree to value appearing before "°" in argument string.
degrees = Val(Left(Degree_DMS, InStr(1, Degree_DMS, "°") - 1))
' Only positive value is computed. Sign of result is changed in final statement
' i.e. -7° 8' 9" means (-7 - 8/60 - 9/3600)
If degrees < 0 Then
degrees = degrees * -1
sign = -1
Else
sign = 1
End If
' 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_DMS, InStr(1, Degree_DMS, "°") + 2, InStr(1, Degree_DMS, "'") - InStr(1, Degree_DMS, "°") - 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_DMS, InStr(1, Degree_DMS, "'") + 2, Len(Degree_DMS) - InStr(1, Degree_DMS, "'") - 2)) / 3600
DMS2Real = sign * (degrees + minutes + seconds)
End Function
Function Real2DMS(dms) ' -77.4359722222 -> -77° 26' 9.5"
DIM sign As String
' allow for negative values
If dms < 0 Then
dms = dms * -1
sign = "-"
Else
sign = " "
End If
'Set degree to Integer of Argument Passed
Degrees = Int(dms)
'Set minutes to 60 times the number to the right
'of the decimal for the variable Decimal_Deg
Minutes = (dms - 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.0")
'Returns the Result of degree conversion
'(for example, 10.46 = 10~ 27 ' 36")
Real2DMS = sign & Degrees & "° " & Int(Minutes) & "' " & Seconds + Chr(34)
End Function
Sub Main
End Sub