PDA

View Full Version : [SOLVED] time degrees to decimal degrees



Aussiebear
09-27-2016, 04:05 AM
Why does the following occur and how do I overcome the issue?

By using the following formula to convert Latitude coordinates to decimal degrees "= Left(A1,2)+Mid(A1,4,2)/60+Right(A1,3)/3600", seems to work IF the coordinate is a mid to low minute & second value, but high minute & second values give a corrupted result.

Example. 22.36.000 results in 22.600000 and 23.00.000 results in yet 22.53.973 results in 23.153611. The last result is fatally wrong.

How do I overcome this?

Then to really complicate matters, a significant majority of my GPS marks are given in the following format 22.53.9731 (GPS data from trawler skippers). 4 digit figures for seconds.

The above formula was adjusted to read "=Left(A1,2)+Mid(A1,4,2)/60+Right(A1,4)/3600" but fails with a result 25.586388, but "=Left(A1,2)+Mid(A1,4,2)/60+Right(A1,4)/360000" results in 22.910363 which appears to be correct.

Wherein lies my error?

SamT
09-27-2016, 05:00 AM
Degrees + Decimal Minutes

=Left(A1,2)+CDbl(Mid(A1,4))/60

Paul_Hossler
09-27-2016, 08:01 AM
Example. 22.36.000 results in 22.600000 and 23.00.000 results in yet 22.53.973 results in 23.153611. The last result is fatally wrong.

The above formula was adjusted to read "=Left(A1,2)+Mid(A1,4,2)/60+Right(A1,4)/3600" but fails with a result 25.586388, but "=Left(A1,2)+Mid(A1,4,2)/60+Right(A1,4)/360000" results in 22.910363 which appears to be correct.



1. Since there is a variety of data formatting, I'd suggest a UDF that is smart enough to handle such things

2. Using the UDF below, 22.53.973 converts to -22.8860361111111 which seems more reasonable

3. The Seconds part sort of has an implied decimal point after the second character so dividing by 3600 should be 3600/100 or 360000

"1" is really 10 sec
"12" is really 12 sec
"123" is really 12.3 sec
"1234" is really 12.34 sec






Option Explicit

Sub drv()

MsgBox DMS2Deg("123")
MsgBox DMS2Deg("123.30")
MsgBox DMS2Deg("123.30.0")
MsgBox DMS2Deg("123.30.1234")

MsgBox DMS2Deg("-123")
MsgBox DMS2Deg("-123.30")
MsgBox DMS2Deg("-123.30.0")
MsgBox DMS2Deg("-123.30.1234")

MsgBox DMS2Deg("123w")
MsgBox DMS2Deg("123.30 w")
MsgBox DMS2Deg("-123.30.0 W")
MsgBox DMS2Deg("-123.30.1234 W")

MsgBox DMS2Deg("22s")
MsgBox DMS2Deg("22.30 s")
MsgBox DMS2Deg("-22.30.0 s")
MsgBox DMS2Deg("-22.30.1234 s")
MsgBox CStr(DMS2Deg("abcedf"))

MsgBox "15.23.4500N" & " = " & DMS2Deg("15.23.4500N")
MsgBox "15.23.4500S" & " = " & DMS2Deg("15.23.4500S")
MsgBox "155.23.4500E" & " = " & DMS2Deg("155.23.4500E")
MsgBox "155.23.4500W" & " = " & DMS2Deg("155.23.4500W")

End Sub


'Positive latitude is above the equator (N), and negative latitude is below the equator (S).
'Positive longitude is east of the prime meridian, while negative longitude is west of the
' prime meridian (a north-south line that runs through a point in England).
Function DMS2Deg(DMS As String) As Variant
Dim v As Variant
Dim s As String
Dim iDir As Long

DMS2Deg = CVErr(xlErrNum)
On Error GoTo ErrorOut

iDir = 1
s = UCase(Trim(DMS))
s = Replace(s, " ", vbNullString)

Select Case Right(s, 1)
Case "S", "W"
'dmsS or dmsW becomes -dms
If Left(s, 1) <> "-" Then
s = "-" & Left(s, Len(s) - 1)
'-dmsS or -dmsW becomes just -dms
ElseIf Left(s, 1) = "-" Then
s = Left(s, Len(s) - 1)
End If
Case "N", "E"
'dmsN or dmsE becomes just dms
s = Left(s, Len(s) - 1)
End Select

If Left(s, 1) = "-" Then iDir = -1

v = Split(s, Application.International(xlDecimalSeparator))

Select Case UBound(v)
Case 0
DMS2Deg = CDbl(v(0))
Case 1
DMS2Deg = CDbl(v(0)) + iDir * (CDbl(v(1)) / 60#)
Case 2
'need to make 1234 into 12.34 or 12 into 12.00
v(2) = Format(v(2), "0000")
v(2) = Left(v(2), 2) & Application.International(xlDecimalSeparator) & Right(v(2), 2)
DMS2Deg = CDbl(v(0)) + iDir * (CDbl(v(1)) / 60#) + iDir * (CDbl(v(2)) / 3600#)
End Select

ErrorOut:

Exit Function
End Function

Aussiebear
09-27-2016, 10:52 AM
Thank you to both of you for your assistance