# DMS to decimal and back again

#### Mattfa

##### New Member
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.

Regards Matt

### 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
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
Correction:
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
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