Convert Latitude and Longitude from Decimal to Degree, Minute, Seconde

Last Updated on 07/03/2021
Reading time: 3 minutes

Nowadays, GPS localization is common. Some apps use decimal format (48.85833) while others return the coordinates in degrees, minutes and seconds (48°51'29.99'').

In this article I will show you how to convert from one format to the other and vice-versa.

Add GPS coordinates to address

If you are looking to add GPS coordinates to your address, please go to this page.

How to write a GPS coordinates in Excel

GPS coordinates are generally written using the symbols ° (degrees), ' (minutes), and '' (seconds).

GPS coordinates as Text

But if you keep your coordinate as a string, no calculation is possible. So it will be impossible to convert the GPS coordinates ⛔⛔⛔

What is the best way to write a GPS coordinate?

Use the following format for your GPS coordinates - hours:minutes:seconds. Each part of the coordinate should be delimited with a colon ":"

48:51:29.99

The default cell display could be different depending on the version of Excel that you are using. It can display the GPS coordinates as either a date or a time.

GPS coordinates write as number

Don't worry, we will correct this formatting soon 😉😎😃

Insert a negative time

The standard GPS coordinate is:

  • positive for East and North
  • negative for West and South

Unfortunately, Excel doesn't accept a negative time, so you need to wrap it in double quotes as though it were text. Then put a minus sign before the opening quote.

=-"74:02:40.29"

But the cell content is displayed as a string of #

Excel doesn't like negative time. But you can correct this by checking one option in  Excel.

  • Go to the menu File>Options
  • Select Advanced
  • At the bottom of the list of options, check the option "Use 1904 date system"
Correction of the negative time in the cells

Customize the time format

Now, let's customize the format of our cell:

  • Open the Format cell dialog box (Ctrl + 1)
  • Select the option Custom
  • In the Type box, enter this format code exactly as it is displayed below

[hh]°mm'ss.00\''

Custom format to display the GPS Coordinates

The hour between square brackets is compulsory to display a value over 23 hours.

Convert coordinates from Degree, Minute, Second (DMS) to decimal

Because we have written the coordinate as a number, we just have to multiply the cell by 24 (24 hours in a day). The number format is General.

Convert Latitude and Longitude to degrees, minutes and secondes

And the the conversion is finished 😍😍😍

=B3*24

Then just change the format of the cell range D3:E7 to General.

Convert coordinates from decimal to Degree, Minute, Second

And the other way round, to convert a decimal value to degrees, minutes and seconds, simply divide the value by 24.

= B3/24

Convert coordinates from degree minute second to Decimal

12 Comments

  1. Jana
    07/03/2022 @ 16:05

    Can't figure out how to input conversion formula from this.

    Reply

  2. Muhammad wasim
    01/12/2021 @ 11:24

    kindly provide me any kind of excel format or program for convert arbitrary coordinates to GPS coordinates.

    Reply

  3. John
    01/10/2021 @ 11:05

    Hi, This seems like a great solution but I'm obviously doing something wrong.

    I'm converting lat and long from decimal and it works fine until the minutes are over 24 and then they reset to zero. The minutes and seconds remain correct though.

    Please help.

    Thanks

    Reply

  4. Igor
    28/01/2021 @ 15:41

    Frederick, thank you so much! It works! You saved hours of my working time!

    Reply

  5. Traian Radulescu
    26/11/2020 @ 10:21

    Hi! Thank you very much for the article. It was very useful for me.
    How can I write the latitude with degrees, minutes and decimals of minutes?
    Just like u wrote here [hh]°mm'ss.00\'', but I want the decimals after the minutes like 10°50'.5
    The minutes need to be divided by 6 to obtain decimals.
    Second question: how do I format the cells so when I calculate latitude 1 + latitude 2, when I have 10 decimals at the end to go automatically in minutes.
    Example : 10°50'.5
    + 20° 5'.6
    =30° 56'.1
    Thank you very much!

    Reply

  6. Jimmy Loki
    26/11/2019 @ 17:43

    Thank you, I tried many tutorials using VBA but failed... you are such a Genius

    Reply

  7. Laura
    14/11/2019 @ 19:24

    I can not make Excel recognize the custom format ..[hh]°mm'ss.00\ it only lets me pick from a select list of formats, none of which is right. Is it my version of Excel?

    Reply

    • Frédéric LE GUEN
      26/11/2019 @ 15:05

      Hi, that's strange. Do you select custom in the list of format ?

      Reply

  8. floyd
    09/07/2019 @ 08:37

    Been reading so many VBA scripts on how to do this DMS to DD conversion within excel
    And even Microsoft's publication of their own VBA script on how to do this
    https://support.microsoft.com/en-ca/help/213449/how-to-convert-degrees-minutes-seconds-angles-to-or-from-decimal-angle
    Even does the calculation part wrong

    Wasted so much time trying to remedy their script, and read other scripts on the internet.
    Your solution was implemented in 1 minute, so so simple, and brilliant.
    Thank you again so much for posting this.

    Reply

    • Frédéric LE GUEN
      23/07/2019 @ 07:19

      Thanks 🙂

      Reply

  9. Sandro
    13/05/2019 @ 10:14

    GENIUS!! Thanks for sharing
    Btw: it misses a double quote at the end of the formula written in black background ([hh]°mm'ss.00\" )

    Reply

  10. rajeshwar pilli
    02/06/2018 @ 11:07

    kindly provide me any kind of excel format or program for convert arbitrary coordinates to GPS coordinates.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Convert Latitude and Longitude from Decimal to Degree, Minute, Seconde

Reading time: 3 minutes
Last Updated on 07/03/2021

Nowadays, GPS localization is common. Some apps use decimal format (48.85833) while others return the coordinates in degrees, minutes and seconds (48°51'29.99'').

In this article I will show you how to convert from one format to the other and vice-versa.

Add GPS coordinates to address

If you are looking to add GPS coordinates to your address, please go to this page.

How to write a GPS coordinates in Excel

GPS coordinates are generally written using the symbols ° (degrees), ' (minutes), and '' (seconds).

GPS coordinates as Text

But if you keep your coordinate as a string, no calculation is possible. So it will be impossible to convert the GPS coordinates ⛔⛔⛔

What is the best way to write a GPS coordinate?

Use the following format for your GPS coordinates - hours:minutes:seconds. Each part of the coordinate should be delimited with a colon ":"

48:51:29.99

The default cell display could be different depending on the version of Excel that you are using. It can display the GPS coordinates as either a date or a time.

GPS coordinates write as number

Don't worry, we will correct this formatting soon 😉😎😃

Insert a negative time

The standard GPS coordinate is:

  • positive for East and North
  • negative for West and South

Unfortunately, Excel doesn't accept a negative time, so you need to wrap it in double quotes as though it were text. Then put a minus sign before the opening quote.

=-"74:02:40.29"

But the cell content is displayed as a string of #

Excel doesn't like negative time. But you can correct this by checking one option in  Excel.

  • Go to the menu File>Options
  • Select Advanced
  • At the bottom of the list of options, check the option "Use 1904 date system"
Correction of the negative time in the cells

Customize the time format

Now, let's customize the format of our cell:

  • Open the Format cell dialog box (Ctrl + 1)
  • Select the option Custom
  • In the Type box, enter this format code exactly as it is displayed below

[hh]°mm'ss.00\''

Custom format to display the GPS Coordinates

The hour between square brackets is compulsory to display a value over 23 hours.

Convert coordinates from Degree, Minute, Second (DMS) to decimal

Because we have written the coordinate as a number, we just have to multiply the cell by 24 (24 hours in a day). The number format is General.

Convert Latitude and Longitude to degrees, minutes and secondes

And the the conversion is finished 😍😍😍

=B3*24

Then just change the format of the cell range D3:E7 to General.

Convert coordinates from decimal to Degree, Minute, Second

And the other way round, to convert a decimal value to degrees, minutes and seconds, simply divide the value by 24.

= B3/24

Convert coordinates from degree minute second to Decimal

12 Comments

  1. Jana
    07/03/2022 @ 16:05

    Can't figure out how to input conversion formula from this.

    Reply

  2. Muhammad wasim
    01/12/2021 @ 11:24

    kindly provide me any kind of excel format or program for convert arbitrary coordinates to GPS coordinates.

    Reply

  3. John
    01/10/2021 @ 11:05

    Hi, This seems like a great solution but I'm obviously doing something wrong.

    I'm converting lat and long from decimal and it works fine until the minutes are over 24 and then they reset to zero. The minutes and seconds remain correct though.

    Please help.

    Thanks

    Reply

  4. Igor
    28/01/2021 @ 15:41

    Frederick, thank you so much! It works! You saved hours of my working time!

    Reply

  5. Traian Radulescu
    26/11/2020 @ 10:21

    Hi! Thank you very much for the article. It was very useful for me.
    How can I write the latitude with degrees, minutes and decimals of minutes?
    Just like u wrote here [hh]°mm'ss.00\'', but I want the decimals after the minutes like 10°50'.5
    The minutes need to be divided by 6 to obtain decimals.
    Second question: how do I format the cells so when I calculate latitude 1 + latitude 2, when I have 10 decimals at the end to go automatically in minutes.
    Example : 10°50'.5
    + 20° 5'.6
    =30° 56'.1
    Thank you very much!

    Reply

  6. Jimmy Loki
    26/11/2019 @ 17:43

    Thank you, I tried many tutorials using VBA but failed... you are such a Genius

    Reply

  7. Laura
    14/11/2019 @ 19:24

    I can not make Excel recognize the custom format ..[hh]°mm'ss.00\ it only lets me pick from a select list of formats, none of which is right. Is it my version of Excel?

    Reply

    • Frédéric LE GUEN
      26/11/2019 @ 15:05

      Hi, that's strange. Do you select custom in the list of format ?

      Reply

  8. floyd
    09/07/2019 @ 08:37

    Been reading so many VBA scripts on how to do this DMS to DD conversion within excel
    And even Microsoft's publication of their own VBA script on how to do this
    https://support.microsoft.com/en-ca/help/213449/how-to-convert-degrees-minutes-seconds-angles-to-or-from-decimal-angle
    Even does the calculation part wrong

    Wasted so much time trying to remedy their script, and read other scripts on the internet.
    Your solution was implemented in 1 minute, so so simple, and brilliant.
    Thank you again so much for posting this.

    Reply

    • Frédéric LE GUEN
      23/07/2019 @ 07:19

      Thanks 🙂

      Reply

  9. Sandro
    13/05/2019 @ 10:14

    GENIUS!! Thanks for sharing
    Btw: it misses a double quote at the end of the formula written in black background ([hh]°mm'ss.00\" )

    Reply

  10. rajeshwar pilli
    02/06/2018 @ 11:07

    kindly provide me any kind of excel format or program for convert arbitrary coordinates to GPS coordinates.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *