13 thoughts on “Decimal Degrees to Degrees-Mins-Secs”

1. If I am not mistaken, you can do the conversion with much simpler formulas…

   Latitude: =TEXT(ABS(A7/24),”[h]° mm’ ss”” “)&IF(A7<0,”S”,”N”)

   Longitude: =TEXT(ABS(A7/24),”[h]° mm’ ss”” “)&IF(A7<0,”W”,”E”)

   where you would still use ALT+0176 to produce the degree symbol.

2. Not to steal your thunder from your next posting on this, but I think the conversion from DMS to decimal can be done with this formula (usable for either latitude or longitude)…

   =IF(OR(RIGHT(B7)={“S”,”W”}),”-“,””)&(24*SUBSTITUTE(SUBSTITUTE(LEFT(B7,LEN(B7)-3),”° “,”:”),”‘ “,”:”))

3. Hmm, I guess you would actually want the conversion from DMS to decimal to be a real number when completed rather than a text value as my last posting’s formula returns. Easy enough…

   =1*(IF(OR(RIGHT(B7)={“S”,”W”}),”-“,””)&(24*SUBSTITUTE(SUBSTITUTE(LEFT(B7,LEN(B7)-3),”° “,”:”),”‘ “,”:”)))

4. We can use the formula structure I posted earlier to devise a relatively short UDF function (keeping your same optional arguments) to convert decimal degrees to degree-minute-second format…

   Function DD2DMS(Degrees As Variant, _
                   Optional Lat As Boolean = True, _
                   Optional Ascii As Boolean = True) As String
     Dim LatLong As String, Mmark As String, Smark As String
     LatLong = Choose(Lat + 2, “”“S”“,”“N”“”, “”“W”“,”“E”“”)
     Mmark = Choose(Ascii + 2, ChrW(39), ChrW(&H2032))
     Smark = Choose(Ascii + 2, ChrW(34), ChrW(&H2033))
     DD2DMS = Evaluate(“TEXT(ABS(“ & Degrees & “/24),”“[h]” & ChrW(176) & ” mm” & _
                       Mmark & ” ss”“”” ““)&IF(“ & Degrees & “<0,” & LatLong & “)”)
   End Function

5. Okay, let’s finish this off with a degree-minute-second to decimal degree UDF…

   Function DMS2DD(DMS As Variant) As Double
     Dim Parts() As String
     Parts = Split(Left(DMS, Len(DMS) – 3))
     DMS2DD = IIf(InStr(“SW”, Right(DMS, 1)), -1, 1) * (Val(Parts(0)) + Val(Parts(1)) / 60 + Val(Parts(2)) / 3600)
   End Function

6. long story short, I have found that displaying in DMS is only usefull when you have a human in the loop. For all calculations keeping it in D.DD is far more useful. Therefore my preference is to maintain it in D.DD, then use basic text functions to generate a string if the user needs it in DMS.

   I also have a concise set of macros to do co-ordinate transformations and also convert lat/lon to the british OSGrid system if anyone may find them useful (can be used for any UTM type grid if you adapt teh constants)

7. Hi Rick –

   Irked at myself for providing the custom format and not taking it that one step more. Thanks. I plead increased mental density. My personal calendar rolled around one more time, and that’s my excuse.

   There’s a ton of MVP-methods in there, too.

   Do you want to write the part about how, for a location’s latitude, lat DMS2DD(DD2DMS(lat)) ;-)

   for all latitudes? Also true for longitude.

   …mrt

8. That should read:

   lat <> DMS2DD(DD2DMS(lat))

9. @Michael,

   >> Do you want to write the part about how, for a
   >> location’s latitude, lat .NE. DMS2DD(DD2DMS(lat))
   >> for all latitudes? Also true for longitude.

   I’m guessing the problem as to do with using whole seconds as the minimum interval for DMS notation. Using but one example, the numbers between 1.23459 and 1.23486 will all convert to the same DMS value, namely, 1° 14′ 05? N. Obviously, converting it back to a decimal again cannot reproduce all of the numbers in the range that created it, so it translates back to the value where the seconds are exactly 5, namely, 1.23472222222222.

10. Hi Rick –

    Don’t know about the “exact” part, but you’ve got it for sure. If you pass the Capitol building through and back, it moves. :-)

    Looks like my next post is written…

    …mrt

11. @Michael,

    Yeah, “exactly” might have been too exacting a word to use. Perhaps something like this is a better way to say it…

    Obviously, converting it back to a decimal again cannot reproduce all of the numbers in the range that created it, so it translates back to the value where the seconds are exactly 5 (with that value being rounded to the precision Excel is able to display it to), namely, 1.23472222222222.

12. my problem: I am monitoring subsidence monuments and I want to add and subtract angular measurements in direct and indirect.

    ie. dir: 203°34’20” ind: 23°34’24” with a mean angle being 203°34’22” on a certain date
    and dir: 203°33’47” ind: 23°33″46″ with a mean angle being 203°33″46″ on another date

    How can I enter this information into excel and formulate the difference of angular measurement?

13. Mate, I’m a photographer not a computer scientist. I own a 1 copy of Excel and many, many cameras.
    I know a lot about photography but nothing about computer programming. But I am most proficient with the “Copy and Paste” business.
    I have not got the foggiest idea of what “VB” is. Nor am I interested in knowing what it is or does.
    I just want to type a decimal position into Cell number A1 and get the result in degrees, minutes and seconds displayed somewhere nearby.
    I don’t give a hoot how it does it as long as it crunches the numbers and displays the correct result.
    If one of you Whiz Kids out there can do that, you’ll save an old bloke most a most severe amount of tedium as I have thousands of these damned things to convert.

Posting code? Use <pre> tags for VBA and <code> tags for inline.