Excel convert degrees to hrs/mins/secs

[Robertson1995]

I need a formula that will convert degrees Lat/Long to hours minutes seconds.

Example:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 width=64 align=right x:num="32.530422219999998">32.53042</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64 align=right x:num="-92.090132999999994">-92.0901</TD></TR></TBODY></TABLE>

Need to convert to:

32 31 49.5114 92 5 24.36 (With spaces in between)

 

[Phox]

Are the latitude and longitude coordinates in separate cells? If so, try:

=IF(A1<0,INT(A1)+1&" "&INT(60*MOD(-A1,1))&" "&ROUND(60*MOD(-A1*60,1),4),INT(A1)&" "&INT(60*MOD(A1,1))&" "&ROUND(60*MOD(60*A1,1),4))

 

[Richard Schollar]

Another possible option is to use a formula like:

=A1/24

and then use a custome number format of:

[h] m s.000

in the cell. You do get rounding with this though in the decimal places.

 

[Robertson1995]

Thanks for your help. That worked great.

 

[Phox]

Richard, I like that idea but it freaks out when you input negative numbers. Do you know what may be causing that?

 

[Richard Schollar]

Oh of course - because excel doesn't handle negaive times! Thanks for pointing that out. You could probably deal with that using:

=IF(A1<0,"-","")&TEXT(ABS(A1/24),"[h] m s.000")

but you do still have the rounding.