GPS tells you where you are in Decimal Degrees (DD), with positive degrees being north latitude or east longitude, and negative degrees being south latitude or west longitude. Most charts (I was a nautical type before I was an Excel type. Think maps if that’s not you) tell you position in degrees-arc minutes-arc seconds (DMS). While one can “eyeball” the conversion from DD to DMS, let’s get more specific. Thanks to Wikipedia, the US Capitol is at 38.889722°, -77.008889°. Ignoring what we might otherwise know, by the GPS sign conventions the Capitol has a north latitude and a west longitude. Making the conversion is about handling remainders. Walking through converting the Capitol’s longitude, the degrees are:

- D = INT(ABS(-77.008889)) or 77

The remainder R is:

- R = ABS(-77.008889) – D
- R = 77.008889 – 77
- R = 0.008889

To convert to arc-minutes:

- M = R*60
- M = 0.008889*60
- M = 0.533340
- M = INT(M) = INT(0.533340)
- M = 0, but always formatted “00”

The remainder is:

- R = 0.533340 – M*60
- R = 0.533340 – 0*60
- R = 0.533340

To convert to arc-seconds:

- S = R*60
- S = 0.533340*60
- S = 32.000400
- S = INT(S) = INT(32.000400)
- S = 32

The Capitol is at 77 degrees, 00 minutes, 32 seconds, and remembering the minus sign, west. Or 77° 00′ 32? W. Alternatively, 77° 00′ 32″ W with ascii substitutions for prime and double prime.

Turning that into VBA, with an optional Boolean to specify if you want north/south or east/west, and another option for Ascii representation, the DD2DMS conversion looks like this:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Function DD2DMS(Degrees As Variant, Optional Lat As Boolean = True, _ Optional Ascii As Boolean = True) As String Dim ArcMins As Variant Dim ArcSecs As Variant Dim NSEW As String * 2 Dim D_mark As String * 1, M_mark As String * 1, S_mark As String * 1 If Lat Then NSEW = IIf(Degrees < 0, " S", " N") Else NSEW = IIf(Degrees < 0, " W", " E") End If D_mark = ChrW(176) If Ascii Then M_mark = ChrW(39): S_mark = ChrW(34) Else M_mark = ChrW(&H2032): S_mark = ChrW(&H2033) End If Degrees = Abs(Degrees) ArcMins = 60 * (Degrees - Int(Degrees)) ArcSecs = 60 * (ArcMins - Int(ArcMins)) Degrees = Int(Degrees) & D_mark & Chr(32) ArcMins = Format(Int(ArcMins), "00") & M_mark & Chr(32) ArcSecs = Format(ArcSecs, "00") & S_mark & NSEW DD2DMS = Degrees & ArcMins & ArcSecs End Function |

The degree character is ascii(176). That’s actually more concise than my explanation. ;-(

Can it be done in a spreadsheet formula? Of course, but it’s complicated, and that’s the reason for the somewhat tedious example above to walk through. **Formulas do modular arithmetic differently than VBA.** In a formula MOD(ABS(-77.008889),1) equals 0.008889. In VBA, ABS(-77.008889) Mod 1 equals zero. We’ll make use of this difference to find our remainders. Assuming the Capitol’s decimal longitude is in H8, then in four parts:

`D = INT(ABS(H8))&CHAR(176)`

Arc-minutes:

`M = TEXT(INT(MOD(ABS(H8),1)*60)," 00")&CHAR(39)`

Arc-seconds:

`S = TEXT(MOD(MOD(ABS(H8),1)*60,1)*60," 00")&CHAR(34)`

Note the nested MOD(,1)’s to get the second remainder. Lastly for east/west:

`EW = IF(H8 < 0," W"," E")`

The longitude is the concatenation of all of these:

`=INT(ABS(H8))&CHAR(176)&TEXT(INT(MOD(ABS(H8),1)*60)," 00")&CHAR(39)&TEXT(MOD(MOD(ABS(H8),1)*60,1)*60," 00")&CHAR(34)&IF(H8 < 0," W"," E")`

Complicated. Can it be done as a number format? Since we are talking minutes and seconds suggests yes. However Excel doesn't process negative times, so the answer will be completely right only for that quarter of the world that is in the northern latitudes and eastern longitudes. Assuming again H8 holds the longitude, than in another cell, `=ABS(H8/24)`

. Custom format that cell as

- [h]° mm' ss"

You enter the degree symbol by holding down the alt-key and keying 0176 on the keypad. How to get the north/south or east/west right will require another cell.

There's two and a quarter ways to make the DD2DMS conversion. A second of angle on the surface of the earth is about 30 meters or 100 feet. ... Degrees, given to three decimal places (1/1000 of a degree), have about 1/4 the precision as degrees-minutes-seconds (1/3600 of a degree), and so identify locations within about 120 meters or 400 feet. That's not true as you add decimal places, of course, and the decimal to sexagesimal (Base10 to Base60) conversion becomes inexact.

More in this in the next post: How to go DMS2DD.

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.

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),”° “,”:”),”‘ “,”:”))

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),”° “,”:”),”‘ “,”:”)))

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…

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

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

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

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)

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.

…mrtThat should read:

@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.

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@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.

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?

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.