wasyoungonce
New Member
- Joined
- Dec 8, 2008
- Messages
- 15
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?
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?
Last edited: