MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Degrees Minutes Seconds


Posted by victorJR on June 25, 2001 3:24 AM

Can anyone help me create a formula or script that will convert "Decimal Degrees" to "Degrees-Minutes-Seconds"??


Posted by victorJR on June 25, 2001 3:26 AM

Here is an explanation of how it is done on paper....

*The whole units of degrees will remain the same (i.e. in 121.135, start with 121°).
*Multiply the decimal by 60 (i.e. .135 * 60 = 8.1).
*The whole number becomes the minutes (8').
*Take the remaining decimal and multiply by 60. (i.e. .1 * 60 = 6).
*The resulting number becomes the seconds (6'). *Seconds can remain as a decimal.
*Take your three sets of numbers and put them together, using the symbols for degrees (°), minutes (‘), and seconds (') (i.e. 121°8'6')

Posted by victorJR on June 25, 2001 3:30 AM

Here is an explanation of how it is done on paper....

*The whole units of degrees will remain the same (i.e. in 121.135, start with 121°).
*Multiply the decimal by 60 (i.e. .135 * 60 = 8.1).
*The whole number becomes the minutes (8').
*Take the remaining decimal and multiply by 60. (i.e. .1 * 60 = 6).
*The resulting number becomes the seconds (6'). *Seconds can remain as a decimal.
*Take your three sets of numbers and put them together, using the symbols for degrees (°), minutes (‘), and seconds (') (i.e. 121°8'6')

Posted by IML on June 25, 2001 11:43 AM

Assuming your number is A1, try the following

=INT(A1)&CHAR(186)&" "&INT((A1-INT(A1))*60)&CHAR(145)&" "&ROUND((((+A1-INT(A1))*60)-INT((+A1-INT(A1))*60)),2)*60&CHAR(145)

good luck

Posted by victorJR on June 26, 2001 5:28 AM

thanks for your help. WORKED PERFECTLY!!!