time degrees to decimal degrees

[Aussiebear]

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]

Degrees + Decimal Minutes

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

[Paul_Hossler]

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]

Thank you to both of you for your assistance