>

Latitude And Longitude In Excel

 This page describes various methods for working with Latitude and Longitude data in Excel. You can download an example workbook here. North, South, East, And West You will find it useful to treat Northern latitudes and Western longitudes as positive, and Southern latitudes and Eastern longitudes as negative.  You may reverse this, if desired, but be sure to be consistent with all coordinates.    Entering Degrees, Minutes, And Seconds You should enter degrees, minutes, and seconds in time format: DD:MM:SS Format the cell as [hh]:mm:ss to properly display values such as 107:30:45.  This works only if you are working with positive coordinates.  If you're working with negative coordinates, you must manually convert them to decimal degrees.   Converting Degrees, Minutes, And Seconds To Decimal Degrees While it may be useful to enter degrees, minutes, and seconds in the time format, these values are not useful for computation.  To convert DMS values to decimal degrees (e.g., 45:30:30 to 45.50833), multiply the DD:MM:SS value by 24, and format the cell as General.  For example, =A1*24 Excel's trigonometric functions work only with Radians, not Degrees, so be sure to convert angular values to radians with the =RADIANS function. Converting Decimal Degrees To Degrees, Minutes, And Second When you do arithmetic with decimal degrees, you should use the =ABS function to ensure that your final result is positive, so that Excel can reformat the result back into a displayable time. (If Excel is using the 1900-Date system, the default, it cannot display "negative" times. )  Once you have computed your result,  divide it by 24 and format the cell as "[h]:mm:ss" to display the result as DD:MM:SS . Displaying Values as Deg� Min' Sec" You may find it useful to display coordinates as Deg� Min' Sec". You can do this with a custom number format.  Select the cell or cells to format, open the Cell Format dialog box from the Format menu, and add a new custom format of [hh]�mm'ss\" To insert the degree symbol �, hold down the ALT key, and enter 0176 on the numeric keypad on your keyboard. You then enter data into these cells in the format dd:mm:ss, including the colons. You can't c  Great Circle Distances I began working with Latitudes and Longitudes to create a worksheet that would allow me to enter the latitudes and longitudes of two cities on Earth, and determine the Great Circle Distance (this "as the crow flies" distance) between them. The formula to compute this distance is: =RadiusEarth*ACOS(COS(RADIANS(90-(Lat1*24)))*COS(RADIANS(90-(Lat2*24)))+ SIN(RADIANS(90-(Lat1*24)))*SIN(RADIANS(90-(Lat2*24)))* COS(RADIANS(24*(Long1-Long2)))) Where Lat1 is the latitude of point 1, entered as DD:MM:SS. Long1 is the longitude of point 1, entered as DD:MM:SS. Lat2 is the latitude of point 2, entered as DD:MM:SS. Long2 is the longitude of point 2, entered as DD:MM:SS. RadiusEarth is the radius of the earth in nautical miles (3443.917 nm or 6378.135 kilometers) You can download an example file here. The function above works only for points in the Northern and Western hemisphere, where Latitudes and Longitudes are considered to be positive.  If you are mixing hemispheres, enter Northern and Western coordinates as positive, and Southern and Eastern coordinates as negative, and use the following formula: =RadiusEarth*ACOS(COS(RADIANS(90-Lat1))*COS(RADIANS(90-Lat2))+ SIN(RADIANS(90-Lat1))*SIN(RADIANS(90-Lat2))*COS(RADIANS(Long1-Long2))) In this formula, Lat1, Long1, Lat2, and Long2 must be entered as decimal degrees (e.g., 45.5 rather than 45:30:00). You can download an example file here. I've got a Excel97 workbook that lists the latitude and longitude for some 1,200 US cities and towns.  It has a userform and code module that lets you search the database for 2 cities, and compute the distance between them.  If you're interested in it,  download it.
 Other Date And Time Related Procedures are described on the following pages. Adding Months And Years The DATEDIF Function Date Intervals Dates And Times Date And Time Entry Date And Time Arithmetic Distributing Dates Over Intervals Holidays Julian Dates Latitude And Longitude  Overtime Hours And Timesheets VBA Procedures For Dates And Times Week Numbers Worksheet Functions For Dates And Times See the Dates And Times Topic Index For Information