DMS to decimal and back again

[Mattfa]

Hi there

I am wondering if anyone can help me. I am working in longitude and latitudes in the southern hemisphere (Latitudes are negative) I have put in the following cell formula

=TEXT(INT(G17),"0° ")&TEXT(INT((G17-INT(G17))*60),"0' ")&TEXT((G17*60-INT(G17*60))*60,"0.0000")&""""

but the negative decimal degrees throws it. for example I have -0.308733139 and should give me -0d 18' 31.4393" but instead it gives me -1d 41' 28.5607"

The other problem I have is once I have this text dms value I need to convert it back into decimal degrees in order to do more calculations with it.

If anyone can please help me I would greatly appreciate it

Regards Matt

 

[AlphaFrog]

Try something like this...

=TEXT(INT(G17),"0° ")&TEXT(INT((G17-INT(G17))*60),"0' ")&TEXT(([COLOR="Red"]ABS([/COLOR]G17[COLOR="Red"])[/COLOR]*60-INT([COLOR="Red"]ABS([/COLOR]G17[COLOR="Red"])[/COLOR]*60))*60,"0.0000")&""""

As far as converting back to decimal, couldn't you just use the value in G17?

 

[AlphaFrog]

Correction:
This should be your formula...

=IF(G17<0,"-","") & TEXT(INT(ABS(G17)),"0° ") &
                    TEXT(INT((ABS(G17)-INT(ABS(G17)))*60),"0' ") & 
                    TEXT((ABS(G17)*60-INT(ABS(G17)*60))*60,"0.0000")&""""

Here are two custom functions to do the conversions. They are from a Microsoft article but I modified them slightly to handle negitive values.

In H17 put this formula to convert the G17 decimal degree value to Deg, Min, Sec
=Convert_Degree(G17)

Function Convert_Degree(Decimal_Deg) As Variant
    
    Dim degrees As Variant
    Dim minutes As Variant
    Dim seconds As Variant
    Dim Neg As Boolean
        
        Neg = Decimal_Deg < 0
        Decimal_Deg = Abs(Decimal_Deg)
        
        '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.0000")
        'Returns the Result of degree conversion
       '(for example, 10.46 = 10~ 27  ' 36")
        Convert_Degree = IIf(Neg, "-", "") & " " & degrees & "° " & Int(minutes) & "' " _
            & seconds + Chr(34)
    
End Function

Use this formula to convert back to decimal degree
=Convert_Decimal(H17)

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
   Dim Neg As Boolean
   
   Neg = Left(Degree_Deg, 1) = "-"
   
   ' Set degree to value before "°" of Argument Passed.
   degrees = Abs(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 = IIf(Neg, -1, 1) * (degrees + minutes + seconds)
End Function

 

[Mattfa]

Thankyou so much Alphafrog it has worked a treat I can not tell you how much this is going to help me. Sorry for taking so long to get back to you.

Cheers Matt