This article will show you how to convert written latitudes and longitudes into Degree, Minute, Second (DMS) in decimal writing. But also the opposite : Decimal coordinate in Degree, Minute and Second.

Data conversion problem

As you can see in the image opposite, a GPS coordinate written with the symbols ° ' and '' transform the GPS reference in text (framing to the left of the cell).

EIf you keep the Text, you will not be able to calculate the conversion. We must therefore transform this writing into a digital format to be able to carry out the conversion.

The only way is to go through the time format so that Excel can interpret it.

Step 1: Write in hourly format

First of all, you need to write your GPS readings in hours, minutes, seconds as if they were an hour, using the separator symbol ""

By entering the value 48:51:29.99, Excel returns the following information to you.

03/01/1904 00:51:30

Already, the value is not displayed as it should and especially why it is 03/01/1904. We will see all this at step 3

Step 2: Change an Excel option to handle negative hours

You cannot enter directly a negative hour in Excel ⛔⛔⛔. To get around this obstacle, you must write the time in quotation marks preceded by the "-" sign, like this

=-"01:30:40"

Only now we have another problem: Excel does not display negative times

To remedy, 2 different solutions are possible. Here we choose the solution of 'Calendar since 1904' to correct this display problem.

Step 3: Customize the hour format

By default, in Excel, as soon as the number of hours exceeds 24 hours, Excel considers it to be one more day. But it is possible to work around the problem by playing on the format of hourly numbers.

• Open format customization (Ctrl+1)
• Select category custom
• In the Type box, enter the type [h]°mm'ss.00\''

GPS coordinates are displayed in Degrees, Minutes, Seconds format and the format is digital. The coordinates of the Eiffel Tower are now displayed correctly

48:51:29.99

Step 4: Last step, do the conversion calculation

Now that we have created our sheet with numbers in Degrees/Minutes/Seconds format, we will create the calculation formula in D3 to convert the coordinates of B3.

To do this, simply multiply each cell By 24 (number of hours in a day?).

= B3 * 24

All you have to do is change the format of the cells to format Standard to display the expected result.

Convert decimal number to Degrees/Minutes/Seconds

Conversely, to convert a decimal value into degrees, minutes, seconds, you must

• First perform a mathematical operation
• Then apply the same custom formatting

To convert a decimal number to Degrees, minutes, seconds, simply divide the value by 24 (24 hours in a day).

=B3/24

Then you apply the same number format that we created in step 3.

Related Articles

• Convert addresses to GPS coordinates
• Create a link to Google Maps from Excel
• Associate the postal code with the department