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 the DMS format is useful for presentation and degree decimal format is more suited for calculation purpose. GIS user 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 - Degree = integer part of decimal value. (e.g. if the value is 45.3470 then Degree will be 45.)

Step 2- 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.3470X60 =20.820 so minutes will be 20)

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

Thus the decimal degree 45.3470 will be 45° 20´ 49´´

Doing it by EXCEL

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

Degrees, Minutes, Seconds 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 – 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 – Click on first row of EXCEL file having data of POIs. (Suppose it is row no 2)

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

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

Step 5 – Repeat the Step 2 to Step 4 for column H to convert Longitude values.

Decimal Degrees to Degrees, Minutes, Seconds

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 - 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 - Double click on next column i.e. cell D2 and enter formula =INT((A2-B2)*60) to get the value of latitude minutes.

Step 3 – Double click on next column i.e. cell E2 and enter the

formula =ROUND((((A2-B2)*60)-INT((A2-B2)*60))*60,0) to get the value of latitude in seconds.

Step 4 – Repeat Steps 1 to 3 for column F2.

Step 5 – Copy the cells C2 to H2 and paste them in remaining rows of the sheet.

Complicated but better Way

The values of Latitude and Longitude is generally written in degrees, minutes and seconds format  with symbols of degree, minutes and seconds and directional alphabet (N, S or E,W) at appropriate place e.g. 76°06’57.6”E. If a list of such values is to be converted in degree decimals then the above mentioned technique will not be suitable. But it is possible to process such type of strings in Excel using formula. For this the string manipulation functions (e.g. LEFT, MID, RIGHT) provided in   Excel can be used. The exact format and details of the string functions can be seen in help of Ms Excel.

The following formula can be used to convert DMS string (76°06’57.6”) to degree decimal (76.116).

=LEFT(B23,FIND("°",B23,1)-1)+(MID(B23,FIND("°",B23,1)+1,(FIND("’",B23,1)-FIND("°",B23,1))-1)/60)+(MID(B23,FIND("’",B23,1)+1,(FIND("”",B23,1)-FIND("’",B23,1))-1)/3600)

The reverse of the above i.e. degree decimal to DMS string (76.116 to 76°06’57.6”) conversion can be done using the following formula

=CONCATENATE(TEXT(INT(B2),"##"),"°",TEXT(INT((B2-INT(B2))*60),"##"),"’",TEXT(((B2-INT(B2))*60-INT((B2-INT(B2))*60))*60,"####.###"),"”")

Copy pasted 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 with formula.