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]

Yep works a treat, below is the full formula for converting DEC DD to DMS:

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

I'll adapt this to RA DD to HMS conversion formula.

I've already written formula for DMS to DD conversions for DEC and RA. They are probably unnecessarily long and tedious but they work ok so I'll keep them. I'll adapt the domain restrictions to my DMS to DD formula but ss you can see I'm not very good at this.

Thanks for the help, you sir are a saint! ray:

 

[wasyoungonce]

I wrote another way to convert decimal hours to RA hours minutes and seconds:

=IF(OR(A1<0,A1>24),"Out Of Bounds",HOUR(A1)&"h "&MINUTE(A1)&"' "&SECOND(A1)&""" ")

An input of 23.99999 returns a value of 23h 59' 59". This clocks over to 0h 0' 0" if I input 23.999999. Which is correct for RA.

I have learned quite alot, especially wrt looking for easier methods.

 

[wasyoungonce]

Ahhh the above is not right it does not return correct values.

 

[Richard Schollar]

Time values in Excel are stored as a fraction of the decimal value 1.00 - hence I think that in the formula in your previous post you just need to divide the A1 value by 24.

 

[wasyoungonce]

Thanks Richard.

=IF(OR(A38<0,A38>24),"Out Of Bounds",HOUR(A38/24)&"h "&MINUTE(A38/24)&"' "&SECOND(A38/24)&""" ")

The above fixes that issue. Although I have many other ways now to do the conversions from DD to DEC...I'm just playing around somewhat.

I noticed that in my formulas converting DMS to DD angles I wrote them so that the input is in DMS with the appropriate signs, aka (h, ' & "). In the conversion RA DMS to DD time, I can input the time such as 10h 30' 00" is converted to 10.5h using:

=ROUND((TRUNC(LEFT(A1,FIND("h",A1)-1))&TRUNC(MID(A1,FIND("'",A1)+2,2))/360+TRUNC(MID(A1,
FIND(" ",A1)+1,2))/6)/10, 5)&"h"

But in conversion RA DD to DMS I have to leave the input without any sign values aka 10.5 in lieu of 10.5h.

So I playing aroud somewhat here trying to fix this.

 

[Richard Schollar]

Assuming you have a cell with 10h 30' 00" in it then you can generate the decimal value from this by using:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"h",":"),"'",":"),"""","")*24

change A1 to suit.

 

[wasyoungonce]

That's very neat compared to my try. My efforts are decidedly complicated.

I noticed I'll have to introduce some rounding on the result values...I'll play with that and get back.

Once again you provide ample food for thought and plenty to play with.

Thanks.

 

[wasyoungonce]

Ok..rounded the results ok to 2 decimal.

formula for converting RA HMS to DD hours (angle):

=ROUND(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(AA1,"h",":"),"'",":"),"""","")*24, 5)&"h"

I'll now fiddle with IF and OR statements to restrict domain of input values.

 

[wasyoungonce]

Ok here is my final shot at converting RA HMS to DD and it works quite well:

=IF(OR(MID(A1,1,1)="-",ROUND((TRUNC(LEFT(A1,FIND("h",A1)-1))&TRUNC(MID(A1,FIND("'",A1)+2,2))/360+TRUNC(MID(A1,
FIND(" ",A1)+1,2))/6)/10,5)>24),"Out Of Bounds",ROUND(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"h",":"),"'",":"),"""","")*24,5)&"h")

This is a "Hybrid" formula of what I was playing with and Richards formula. It needs a little clean up of some badly placed brackets.

I had to combine the two to get the "OR" domain statements working. If HMS input value was lets say:-2h 00' 00" (it should never be -ve for RA) or >24h (aka 24h 00' 01") then the only way I could get a function to give an "OR" return as "out of bounds" was to be able to calculate the cell input value.

Trying to use an OR statement like below will not work if there is a -ve 1st character, it just returns "#value" if input cell -ve:
=IF(OR(LEFT(A1,1)="-",ROUND(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"h",":"),"'",":"),"""","")*24,5)>24),"Out Of Bounds",ROUND(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"h",":"),"'",":"),"""","")*24,5)&"h")

So much to learn!

 

[wasyoungonce]

Finally I wrote an easier way to convert negative (and positive) DMS to DD for Declination:

=IF(LEFT(A1,1)="-",ROUND(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"°",":"),"'",":"),"""","")*-24,5)&"°",
ROUND(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"°",":"),"'",":"),"""","")*24,5)&"°")

Basically after try's of different methods (to convert -ve DMS angles to DD) , I found subbing out the "-" with an empty text " " character the easiest method. All I had to do then was round the value to reduce nuisance returns.

All is good.

I am now going to try some domain restrictions for the above to restrict the the formula to return input values for: -90° 00' 00" < input > 90° 00' 00" otherwise "Out Of Bounds". However I think this may end up with an overly complicated formula.

Just venting somewhat here...