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?

 

[Richard Schollar]

Hi

A possible alternative should is the following:

Assuming your decimal value in A1 then:

=IF(A1<0,"-"&TEXT(ABS(A1)/24,"[hh]\ºmm\'ss\"""),TEXT(A1/24,"[hh]\ºmm\'ss\"""))

 

[wasyoungonce]

Thanks Richard...that is so cruel. You nailed it in a much simpler neater manner that ever entered my head.

The only thing I see that my method does offer is that I can get the value returned to be expressed with decimal places after the seconds. I was generally working with all my formulas to be accurate and display to 1 decimal in seconds.

Astronomy angles are referred to as DMS with points of a second for the remainder, ie:-09° 09' 09.9".

The decimal seconds is not a big thing as any accuracy beyond "one second" is pretty much beyond my needs (or most amateurs for that matter).

Thanks for your input. Very much appreciated.

 

[Richard Schollar]

If you want the seconds decimals then:

=IF(A1<0,"-"&TEXT(ABS(A1)/24,"[hh]\ºmm\'ss.00\"""),TEXT(A1/24,"[hh]\ºmm\'ss.00\"""))

 

[Richard Schollar]

If you want the seconds decimals then:

=IF(A1<0,"-"&TEXT(ABS(A1)/24,"[hh]\ºmm\'ss.00\"""),TEXT(A1/24,"[hh]\ºmm\'ss.00\"""))

 

[wasyoungonce]

Yep your method also works for converting Right Ascension Decimal Hours (RA DH) to Hours Minutes and Seconds (HMS):


=IF(A1<0,"-"&TEXT(ABS(A1)/24,"[hh]\hmm\'ss\"""),TEXT(A1/24,"[hh]\hmm\'ss\"""))

An input of 23.999 returns a value of 23h59'56", which is correct. Your method also gets rid of nuisance values, ie: 23.9999 returns 24h00'00".

Thanks again Richard.


Edit:
Internet is playing up here so my posts are out of sync.

I just saw you post on decimal seconds...Sigh...you mean to say I've spent a few weeks off and on this getting it working and you did it in seconds (pardon the pun)...sigh!

 

[wasyoungonce]

Sorry just a few minor things your patience is admirable

Astronomy convention is to leave a space between DMS characters ie:
10h00'00" is written as 10h 00' 00" can I insert spaces between characters? I tried but it would not accept the format change.

Also I mentioned I tried to restrict the domain of the input values so that if the input angles (for DEC) are greater than 90° or less then than -90° the formula returns a "FALSE" or "INPUT" ...but I found my "IF" method was really causing more trouble than worth so I left it for later. Here is what I tried:

=IF(A1<=90,IF(A1<-90,"FALSE", TRUNC(A1)&"°......and so on.

The above wouldn't work as I was trying to string 3 if functions together. Should I be trying "OR" to combine the 2 domain restrictions?

Sorry I was busy with getting the formula working and haven't spent much time trying to restrict the domains so what I have so above is pretty crude.


edit:
sorry my Internet is all over the place and there is a major delay in my ability to post.

Edit Edit:

Fixed the Spaces problem:
=IF(A1<0,"-"&TEXT(ABS(A1)/24,"[hh]\º mm\' ss\"""),TEXT(A1/24,"[hh]\º mm\' ss\"""))

That was easy!

 

[wasyoungonce]

Sorry just a few minor things your patience is admirable

Astronomy convention is to leave a space between DMS characters ie:
10h00'00" is written as 10h 00' 00" can I insert spaces between characters? I tried but it would not accept the format change.

Also I mentioned I tried to restrict the domain of the input values so that if the input angles (for DEC) are greater than 90° or less then than -90° the formula returns a "FALSE" or "INPUT" ...but I found my "IF" method was really causing more trouble than worth so I left it for later. Here is what I tried:

=IF(A1<=90,IF(A1<-90,"FALSE", TRUNC(A1)&"°......and so on.

The above wouldn't work as I was trying to string 3 if functions together. Should I be trying "OR" to combine the 2 domain restrictions?

Sorry I was busy with getting the formula working and haven't spent much time trying to restrict the domains so what I have so above is pretty crude.


edit:
sorry my Internet is all over the place and there is a major delay in my ability to post.

Edit Edit:

Fixed the Spaces problem:
=IF(A1<0,"-"&TEXT(ABS(A1)/24,"[hh]\º mm\' ss\"""),TEXT(A1/24,"[hh]\º mm\' ss\"""))

That was easy!

Edit Edit Edit:
Your help have given me new direction and much to try. Thanks!

 

[Richard Schollar]

Regarding the domain restriction you could use an OR:

=IF(OR(A1< -90,A1>+90),"Out Of Bounds",...)

 

[wasyoungonce]

Thanks Richard I'll give it a try