Convert decimal to degrees/minutes/seconds angles in Excel.
August 10th, 2009We needed to convert GPS waypoints which were in Excel and in decimal form to degree, minute, second form. Logically, we wanted an Excel function to do this, rather than do the whole thing by hand.
I found a link to microsoft which gives a horribly broken implementation of the functionality. Take a look and see if you can spot the fail.
Here is, what I believe to be, a working Excel 2007 function which will convert decimal latitude and longitude into degrees. This likely will work in other version of Excel.
You of course have to use this at your own risk; I assume no liability for whatever you do with this.
Function Convert_Degree(Decimal_Deg, DegType) As Variant With Application Degrees = Fix(Decimal_Deg) minutes = (Decimal_Deg - Degrees) * -60 seconds = (minutes - Fix(minutes)) * -60 seconds = Round(seconds, 4) If DegType = "Lat" Then If Decimal_Deg < 0 Then Direction = " W" Else Direction = " E" End If End If If DegType = "Lon" Then If Decimal_Deg > 0 Then Direction = " N" Else Direction = " S" End If End If If Decimal_Deg < 0 Then Degrees = Degrees * -1 End If If minutes < 0 Then minutes = minutes * -1 End If If seconds < 0 Then seconds = seconds * -1 End If Convert_Degree = " " & Degrees & "° " & Fix(minutes) & "' " _ & seconds & Chr(34) & Direction End With End Function
To use this function, open Excel, press Alt-F8. Paste the above code in. Press Alt-F8. In your worksheet, use =Convert_Degree(cellreference,”type”) where cellreference is the cell that you want to convert and degreetype is either Lat or Long.
