How to Convert Degrees, Minutes, Seconds to Decimal Degrees in Excel


How to convert DMS to decimal with Excel.

Working in civil engineering I run into many situations where I need to convert Degrees, usually as Latitude and/or Longitude,  between decimal degrees and Degrees, minutes, seconds (DMS).  Often this happens in using Lat/Long provided from the survey crew and then needing to use that information to look up government data (such as soils, or flood data).  There are plenty of online converters, but I typically work in MS Excel so it is nice to have a formula or macro to perform the task.

Microsoft provides a cut and paste Visual Basic program to perform the conversion.  It gives step by
step instructs to insert the code on the MS support site.  This method works well, but can only be saved in spreadsheets that have macros enabled. This is not my favorite method.

You can read more about and get the code snippets here: Microsoft

Doing a Google search for the Excel formulas shows that there several ways to structure the formula.
The following is the method that I settled on and seems to work well for me.  Hopefully it works for you or at least provides a good starting point.

Typically, I am entering the numbers by hand, so I don’t have any specific formatting issues to work around.  If you are getting your data in spreadsheet with an existing format, you may have to do some work to “strip” the right data out.  Often, this is a simple text-to-columns operation that can found under the DATA tab.

I started with a simple 4 column x 6 row grid.

This let me label the row and column headings to be consistent.

The first grouping has me enter the Decimal and then returns the DMS.  The second grouping has me
enter the DMS, separated into individual cells (for convenience).  I applied a light fill to the cells I enter data into.

 

Decimal

Degree

Minutes

Seconds

Northing

40.484963

40

29

5.8668

Easting

79.403523

79

24

12.6828

 

 

Northing

40.484963

40

29

5.87

Easting

79.403523

79

24

12.68

 Next, I needed to come up with the formulas to do the conversions. 

To convert from decimal to DMS:

  1. you first need to strip the decimal portion away to be left with the degrees.  I use the INT function for this because it rounds the number down to the closest integer.

The formula looks like this: =INT(B2)

2. To calculate the minutes, you need the decimal part of the original number times 60.  I obtain that number by subtracting the integer from the original number.  That returns a number with a fraction.  again, we only want the integer part of the answer.

This formula looks like: =INT((B2-C2)*60)

3. Finally, to get the seconds, we need to get the decimal portion of the minutes and multiply that by 60.

The final formula is: =((B20-C20)*60-D20)*60  we can keep this number as an integer with decimal.

 See the completed table below.

 

Decimal

Degree

Minutes

Seconds

Northing

40.484963

40

29

5.8668

Easting

79.403523

79

24

12.6828

 The table with formulas exposed looks like this:

 

Decimal

Degree

Minutes

Seconds

Northing

40.484963

=INT(B2)

=INT((B2-C2)*60)

=((B20-C20)*60-D20)*60 

Easting

79.403523

79

24

12.6828

If you want to be able to copy the DMS answer directly out to a web application or to a word document you can add a column to combine the separate cells and add in the text portions.

That formula is: =CONCATENATE(C2,”°”,D2,”‘ “,E2,””””) . Note the extra “ in the formula. Excel requires this to know that you want to add the quote as text.

To convert from DMS to decimal:

For this process, I left it that I enter the parts of the DMS into individual cells.  This simplifies the formula.

I was able to compress the conversion formula into one step. Essentially we just need to divide each portion by 60 and add it to the next higher portion.  Divide the seconds by 60 and add that number to the integer for the minutes.  Then, that decimal is added to the degrees integer to give you the final result.

The equation is: =ROUND((E23/60+D23)/60+C23,6)  I added the ROUND function here to control the precision of the answer, it is not strictly necessary.

 

Decimal

Degree

Minutes

Seconds

Northing

40.484963

40

29

5.87

Easting

79.403523

79

24

12.68

 The table with formulas exposed looks like this:

 

Decimal

Degree

Minutes

Seconds

Northing

=ROUND((E23/60+D23)/60+C23,6) 

40

29

5.87

Easting

79.403523

79

24

12.68

Note: The degree symbol is not simply a superscript “o”.  To obtain a true degree symbol you hold the “ALT” key and type 0176.

 

 

 

Leave a comment