Decimal Degrees to Degrees Minutes and Seconds

[wasyoungonce]

Hi kind folks,

I've writing excel formulas to convert Decimal Degrees (DD) to Degrees Minutes and Seconds (DMS) and back again for astronomy. These are for converting DD angles for telescope Right Ascension (RA) and Declination Angles (DEC) pointing.

I've got the formulas to work except for one minor annoyance. Try this formula for converting DEC DD angle of -1.5° to a DEC DMS angle:

=IF(LEFT(C17,1)="-",CHAR(150)&-1*TRUNC(C17)&"° "&TRUNC((ABS(C17)-ABS(TRUNC(C17)))*60)&"' "&ROUND(ABS((C17*60)-TRUNC(C17*60))*60,1)&"""",TRUNC(C17)&"° "&TRUNC((ABS(C17)-ABS(TRUNC(C17)))*60)&"' "&ROUND((C17*60-TRUNC(C17*60))*60,1)&"""")

The above returns a value of -1° 30' 00" which is correct except I need the returned value to be double figure for the degree part of the angle, ie: -01° 30' 00" (this is just an Astronomy convention thing).

I'm at an Impasse at the moment.

Anyone willing to help?

I also wrote formulas for converting DMS angles back and these work ok.

I also tried to restrict the domain of the input values so that if the input angles are greater than 90° or less then than -90° (for DEC) the formula returns a "FALSE"...but I found these were really causing more trouble than worth. I'll fiddle with that later.

I wrote my own VBS macros to do the conversions and it's easy to restrict domains and values with VBS...not so with formulas in excel.

Most of the macros/formulas I found on the internet either had flaws or didn't work correctly.

Anyone with a kind heart willing to try?

 

[wasyoungonce]

Decimal Degrees to Degrees Minutes and Seconds and back again

Allll righty, here are my final formulas for Astronomical angle conversions. They may be a little "complicated" because I'm not too hot at this but they do work.


To convert Right Ascension (RA) Decimal Hours (Dh) to Hours Minutes and Seconds (HMS), restricting the domain to greater than 0 Hours and Less than 24Hours. Input cell (A1) must be in "text" format with Hour symbol, eg:12.34567h (is converted to 12h 20' 44.4" (using the below formula)):

=IF(OR(LEFT(A1,1)="-",ABS(SUBSTITUTE(A1,"h",""))>24),"Out Of Bounds",TEXT(ABS(SUBSTITUTE(A1,"h",""))/24,"[hh]\h mm\' ss.0\"""))


To convert Declination (DEC) Decimal Degrees (DD) to Degree Minutes and Seconds (DMS), restricting domain to greater than -90 degrees Hours and Less than 90 degrees. Input cell (A1) must be in "text" format with degree symbol, eg:12.34567° (is converted to 12° 20' 44.4" (using the below formula)):

=IF(OR(ABS(SUBSTITUTE(A1,"°",""))<-90,ABS(SUBSTITUTE(A1,"°",""))>90),"Out Of Bounds",IF(LEFT(A1,1)="-","-"&TEXT(ABS(SUBSTITUTE(A1,"°",""))/24,"[hh]\º mm\' ss.0\"""),TEXT(ABS(SUBSTITUTE(A1,"°",""))/24,"[hh]\º mm\' ss.0\""")))

To convert RA HMS to Dh, restricting domain to greater than 0 Hours and Less than 24Hours. Input cell (A1) must be in "text" format, eg: 12h 20' 44.4" (can input decimal seconds)(is converted to 12.34567h using the formula below):

=IF(LEFT(A1,1)="-","Out Of Bounds",IF(ROUND(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"h",":"),"'",":"),"""","")*24,5)>24,"Out Of Bounds",ROUND(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"h",":"),"'",":"),"""","")*24,5)&"h"))

To convert DEC DMS to DD, restricting domain to greater than -90 degrees and Less than 90 degrees. Input cell (A1) must be in "text" format, ie:12° 20' 44.4" (can input decimal seconds)(is converted to 12.34567° using the formula below) :

=IF(OR(ROUND(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"°",":"),"'",":"),"""","")*-24,5)<-90,ABS(ROUND(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"°",":"),"'",":"),"""","")*-24,5))>90),"out Of Bounds",IF(AND(ROUND(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"°",":"),"'",":"),"""","")*-24,5)<0,LEFT(A1,1)="-"),ROUND(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"°",":"),"'",":"),"""","")*-24,5)&"°",ROUND(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"°",":"),"'",":"),"""","")*24,5)&"°"))

I posted my final formulas so other users may learn from my trials and tribulations. There are many more way's to write the formulas (I have trialed many), but, in many cases they fail at the last hurdle. At least these work as I needed.

My excel macros were a quantum easier to write.

Thanks for your help Richard.