Function for converting angles from DMS to hours?

Hi Donny,

Just enter the value in a cell in deg:mm:ss and multiply this cell value by 1.6 to get the angle in hours. If the value you enter is larger than 24 degrees, it will not display as deg:mm:ss in this cell because Excel is interpreting this as a date-time value--nevertheless, the value that is output in decimal hours will still be correct.

Happy computing and star watching.

Damon

Thanks Damon!

Re: another way to convert DMS to hours

Donny,

Here's some code I think you will find interesting. It allows you to enter a value in DMS in a cell as a string with degree symbol, minutes symbol, etc., and converts it to decimal hours. For example the value

30o15'45"5 (30 degrees, 15 minutes, 45.5 seconds)

where the "o" is actually the degrees symbol, will produce the result 2.017509 hours.

Check it out, and let me know if I missed anything. I checked it pretty thoroughly myself, but you never know...

Damon

Function DMStoHours(DMS As String) As Double

' Convert string of the form DDDoMM'SS"SS to astronomical angle
' in hours. the "o" represents the degree symbol, obtainable by
' holding the Alt key and typing 0176 on the numeric keypad. The
' SS"SS represents seconds and fractions of seconds. The
' fractional part is optional. The following forms are
' acceptable:

' 5o35'55"23 Five degrees, 35 minutes, 55.23 seconds
' -105o30'6" Negative angle of 105 degrees, 30 minutes, 6 seconds
' 90o Ninety degrees (minutes and seconds are optional)
' 45'2" Zero degrees, 45 minutes, 2 seconds
' 90o2" Ninety degrees, zero minutes, 2 seconds

Dim Dch As Integer 'position of the deg symbol in string
Dim Mch As Integer 'position of '
Dim Sch As Integer 'position of "
Dim iCh As Integer 'current parsed characters (from left)
Dim Negative As Boolean
Dim Degrees As Double
Dim Minutes As Double
Dim Seconds As Double
Dim FracSecs As Double
Dim FracDigits As Integer
Dim D As Double 'value in decimal degrees

Dch = InStr(2, DMS, Chr(176))
Mch = InStr(2, DMS, "'")
Sch = InStr(2, DMS, """")

Degrees = 0
Minutes = 0
Seconds = 0

Negative = (Left(DMS, 1) = "-") 'True of value is negative
If Negative Then iCh = 2 Else iCh = 1

' Get degrees part
If Dch <> 0 Then
Degrees = CDbl(Mid(DMS, iCh, Dch - iCh))
iCh = Dch + 1
End If

' Get minutes part
If Mch <> 0 Then
Minutes = CDbl(Mid(DMS, iCh, Mch - iCh))
iCh = Mch + 1
End If

' get seconds part
If Sch <> 0 Then
Seconds = CDbl(Mid(DMS, iCh, Sch - iCh))
iCh = Sch + 1
If Len(DMS) >= iCh Then
' there are fractional seconds
FracDigits = Len(DMS) + 1 - iCh
FracSecs = CDbl(Mid(DMS, iCh, FracDigits)) / 10 ^ FracDigits
Seconds = Seconds + FracSecs
End If
End If

' Now form entire angle in degrees
D = Degrees + ((Minutes + Seconds / 60)) / 60

' Now convert angle to hours (1 hour = 15 degrees)
DMStoHours = D / 15

If Negative Then DMStoHours = -DMStoHours

End Function

Thanks Damon!

: Hi Donny, : Just enter the value in a cell in deg:mm:ss and multiply this cell value by 1.6 to get the angle in hours. If the value you enter is larger than 24 degrees, it will not display as deg:mm:ss in this cell because Excel is interpreting this as a date-time value--nevertheless, the value that is output in decimal hours will still be correct. : Happy computing and star watching. : Damon :