Convert DMS to Decimal Degrees

The latitude and longitude values are represented in Degree, Minutes and Seconds (Sexagesimal numeral system). The various GIS software requires the values of the points in terms of degree decimals instead of DMS. The data points collected from field (by a GPS device) may be in Degree Minutes and Seconds. Both the formats have their own utility \u2014 the DMS format is useful for presentation and degree decimal format is more suited for calculation purpose. GIS users very often struggle with the problem of converting the points of interests (POIs) between these two formats.

DMS to Degree Decimal

Following formula can be used to convert DMS to degree decimal values. It can be easily implemented using Visual Basic, C or Java to process large number of POIs. This formula can also be used in MS EXCEL for converting the list of POIs.

Degree Decimal = Degree + (Minutes / 60) + (Seconds / 3600)

Degree Decimal to DMS

The conversion from Degree decimal to degrees, minutes and seconds is not that straight. To convert the decimal degree values use following steps:

Step 1 \u2014 Degree = integer part of decimal value. (e.g. if the value is 45.3470 then Degree will be 45.)

Step 2 \u2014 Multiply fraction part of value by 60 and use the integer part of result for minutes. (e.g. if the value is 45.3470 then 0.3470\u00d760 = 20.820 so minutes will be 20)

Step 3 \u2014 Multiply fraction part from Step 2 by 60 and use the result for seconds. (e.g. if the value from step 2 is 20.820 then 0.820\u00d760 = 49.2 so seconds will be 49 or 49.2)

Thus the decimal degree 45.3470 will be 45\u00b0 20\u2032 49\u2033

Doing it in Excel

Now we can put the above mentioned formulae in MS EXCEL to convert the long list of POIs collected from field.

DMS to Decimal Degrees

Suppose that the values of latitude degrees, minutes and seconds are written in three different columns A, B and C and Longitude degrees, minutes and seconds are written in three different columns D, E and F. Follow the underlying steps:

Step 1 \u2014 Open the excel file in MS Excel having the latitude and longitudes of POIs with values of degrees, minutes and seconds separated in different columns.

Step 2 \u2014 Click on first row of EXCEL file having data of POIs. (Suppose it is row no 2)

Step 3 \u2014 Now double click on cell G2 and enter formula =A2+(B2/60)+(C2/3600) and press enter.

Step 4 \u2014 Copy the formula of G2 cell and paste it in all the remaining cells of column G having data values.

Step 5 \u2014 Repeat Steps 2 to 4 for column H to convert Longitude values.

Decimal Degrees to DMS

Suppose that the values of latitude and longitudes are in column A and B in degree decimal format. To convert degree decimal to DMS follow the given steps:

Step 1 \u2014 Double click on first data row of column C (suppose it is C2) and enter the formula =INT(A2) to get the value of latitude degree.

Step 2 \u2014 Double click on next column i.e. cell D2 and enter formula =INT((A2-C2)*60) to get the value of latitude minutes.

Step 3 \u2014 Double click on next column i.e. cell E2 and enter the formula =ROUND((((A2-C2)*60)-INT((A2-C2)*60))*60,0) to get the value of latitude in seconds.

Step 4 \u2014 Repeat Steps 1 to 3 for Longitude column.

Step 5 \u2014 Copy the cells and paste them in remaining rows of the sheet.

Advanced: DMS String Conversion

The values of Latitude and Longitude are generally written in degrees, minutes and seconds format with symbols (e.g. 76\u00b006\u201957.6\u201dE). If a list of such values is to be converted to degree decimal format for GIS software, the above method will not work since the Excel data has a DMS string in a single cell of data type \u201ctext\u201d.

The following formula converts a DMS string (76\u00b006\u201957.6\u201d) to degree decimal (76.116):

=LEFT(B23,FIND("\u00b0",B23,1)-1)+(MID(B23,FIND("\u00b0",B23,1)+1,(FIND("\u2019",B23,1)-FIND("\u00b0",B23,1))-1)/60)+(MID(B23,FIND("\u2019",B23,1)+1,(FIND("\u201d",B23,1)-FIND("\u2019",B23,1))-1)/3600)

The reverse \u2014 degree decimal to DMS string (76.116 to 76\u00b006\u201957.6\u201d) \u2014 can be done using:

=CONCATENATE(TEXT(INT(B2),"##"),"\u00b0",TEXT(INT((B2-INT(B2))*60),"##"),"\u2019",TEXT(((B2-INT(B2))*60-INT((B2-INT(B2))*60))*60,"####.###"),"\u201d")

Copy paste the above formula (including = sign) in cell D2 and then copy paste from cell D2 to cell E2 to convert the values of DMS strings in cell B2 and C2 into degree decimal values.

Download Sample Excel File