Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

help with converting Latitude & Longitude degreeminute format to decimal degrees

Latest post Wed, Jan 30 2008 4:19 PM by Anonymous. 2 replies.
  • Tue, Jan 29 2008 6:48 PM

    help with converting Latitude & Longitude degreeminute format to decimal degrees

     I am in need of some help in converting latitude and longitude position data within an excel spreadsheet.

     

    The latitude and longitude data is gathered from a SQL database and it shows up in my excel spreadsheet in the following formats:

    Longitude is DDDMM.mm

    where DDD is degrees of longitude, MM is minutes of longitude, and mm is decimal minutes of longitude

    Example: 12151.57 which equals 121 degrees 51.57 minutes of longitude.

     

    Latitude is similar and is in the form of DDMM.mm

    where DD is degrees of latitude, MM is minutes of latitude, and mm is decimal minutes of latitude

    Example: 3630.00 which equals 36 degrees 30.00 minutes of longitude.

     

    Degrees and minutes are sandwiched together. I don't know how to seperate the minutes away from the degrees numbers, so I can divide the minutes by 60 to get decimal degrees. 

     

    I am dealing with tens of thousands of these lat 7 Lon positions within 2 excel columns.  One column has the latitudes and one column has the longitudes.  I need a formula that would convert the DDDMM.mm into DDD.ddd for longitude and convert DDMM.mm into DD.dd for latitude, where D is degrees and d is decimal degrees.

     

    Examples of the conversions I need:

     

    12145.00 needs to convert into 121.75

    3630.00 needs to convert into 36.5

     

    Mapping and contouring software can't handle minute or decimal minutes.  They need degrees and decimal degrees.

     

    I think there is probably a somewhat simple formula for this conversion to decimal degrees.  I just can't seem to figure it out.  I am also new to excel so I could use help in exactly how to write the formula within excel that would act upon the column of position data. 

     

    Thanks for any help or advise.

     

    Ken 

    • Post Points: 21
  • Tue, Jan 29 2008 10:03 PM In reply to

    • Omar
    • Top 10 Contributor
    • Joined on Sat, Jan 12 2008
    • Kitchener, ON
    • Posts 193
    • Points 2,622

    Re: help with converting Latitude & Longitude degreeminute format to decimal degrees

    This formula worked on your two examples. The FLOOR function basically rounds down to the nearest significant digit (in this case, 100's). 

    =FLOOR(A1,100)/100+(A1-FLOOR(A1,100))/60

    Hope that makes sense.

    Omar

    Omar Freeman Kitchener, ON

    • Post Points: 21
  • Wed, Jan 30 2008 4:19 PM In reply to

    Re: help with converting Latitude & Longitude degreeminute format to decimal degrees

     Thanks Omar!

     

    Works like a charm.

     

    Much appreciated

     

    Ken 

    • Post Points: 5
Page 1 of 1 (3 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.