Category: Article

Tags

Published: August 8, 2012

Converting DD to DMS in Excel

This is a handy Excel formula I wrote to convert a coordinate in decimal degrees to degrees, minutes, and seconds.

This is a handy Excel formula I wrote to convert a coordinate in decimal degrees to degrees, minutes, and seconds:

  • Latitude:

    =CONCATENATE(TEXT(ROUNDDOWN(ABS([Lat Cell]),0),"00"),"° ",TEXT(ROUNDDOWN(ABS(([Lat Cell]-ROUNDDOWN([Lat Cell],0))60),0),"00"),"' ",TEXT(TRUNC((ABS(([Lat Cell]-ROUNDDOWN([Lat Cell],0))60)-ROUNDDOWN(ABS(([Lat Cell]-ROUNDDOWN([Lat Cell],0))60),0))60,2),"00.00"),"""",IF([Lat Cell]<0," S"," N"))

  • Longitude:

    =CONCATENATE(TEXT(ROUNDDOWN(ABS([Long Cell]),0),"000"),"° ",TEXT(ROUNDDOWN(ABS(([Long Cell]-ROUNDDOWN([Long Cell],0))60),0),"00"),"' ",TEXT(TRUNC((ABS(([Long Cell]-ROUNDDOWN([Long Cell],0))60)-ROUNDDOWN(ABS(([Long Cell]-ROUNDDOWN([Long Cell],0))60),0))60,2),"00.00"),"""",IF([Long Cell]<0," W"," E")) Just change the text in brackets ([Lat/Long Cell]) to point to the DD cell you want to convert to DMS. If you want a different number of decimal places for the seconds add the desired number of 0's after the decimal point in the "00.00" part and also set the "2" to the number of places.

EDIT: I've fixed the formula to properly handle the situation Brian points out in the comments. The TEXT function was rounding 59.9999999… to "60.00." I've added a TRUNC function so that should not happen.