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

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).

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