DMS to decimal and back again

Mattfa

New Member
Joined
Nov 12, 2010
Messages
2
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,265
Try something like this...
Code:
=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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,265
Correction:
This should be your formula...
Code:
=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)
Code:
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)
Code:
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

New Member
Joined
Nov 12, 2010
Messages
2
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,916
Messages
5,471,488
Members
406,766
Latest member
Parasoner

This Week's Hot Topics

Top