Calculations in terms of degrees minutes seconds in excel , libreoffice/openoffice or any other free spreadsheet

Question

My dad's gotten into astrology, so he needs to do calculations for it - and most books use the Degrees Minutes Seconds nonmenclature - is there any way to set excel or open office calc to handle calculations in terms of DMS?

Edit: We're currently using excel 2007

Edit2: I suspect installing open office / libre office will be an option, and there may not be a MS Excel way of doing it - so i widening the question, Office 2007 is preferred but pretty much any common windows spreadsheet will do

Edit 3: Apparently using time as a workaround dosen't allow for multiplication- which is needed in this case

Answer 1

There's no way to tell Excel to switch between radians and degrees when using functions like e.g.

=SIN(DEGREES)

so you would either have to do explicit conversions in each formula, e.g.

=SIN(RADIANS(DEGREES + MINUTES/60 + SECONDS/3600))

or you could create a custom formula in VBA like

Function SinDMS(Deg As Double, Min As Double, Sec As Double) As Double

    SinDMS = Sin((3.1415 * 2 * (Deg + Min / 60 + Sec / 3600)) / 360)

End Function

(But it will be quite slow if there are many calculations)

Answer 2

Out of the box Excel has no support for what you are asking. There might be plugins around that help with this, but instead I'd like to offer a little work-around:

You can format numbers as time to get Excel (and probably OOo and LibreOffice) to display and calculate values that as you need with the correct divisions, bases, etc.

Using the following custom format (Excel, OOo and LibreOffice will have equivalents but I don't have a copy to hand to test) will render them with the correct symbols as well. The [h] prevents the hour resetting to zero as it reaches 24 allowing arbitrarily large angles. If you'd like leading zero's then just double-up each letter.

[h]º m' s\"

These can be added together, etc, as necessary (they are just numbers underneath, after all). However, this will only work if the final result is positive; Excel will not will render negative times.

If you need to convert this value in to a decimal degrees value, you just need to multiply by 24, because a "day" in Excel time format has a value of 1 (so an hour is 1/24th, etc).

Just make sure the cell this formula goes in has normal formatting - not time:

=A1*24

Alternatively, you can also extract specific components from the time value by using the following:

=INT(A1*24)    returns degrees
=MINUTE(A1)    returns minutes
=SECOND(A1)    returns seconds