SOLVED

Angles

Copper Contributor

Good day all

Does anyone know how to convert an angle (or time) into a decimal format and then visa versa

9 Replies
Not sure if I understand your question. Do you mean convert from degrees into radians?
Hi Johnthefisherman
No .... I would like to decimalize an angle ie: 30 degrees 30 minutes 30 seconds = 30.50833336
and then do it backwards ie: 30.50833336 = 30.30.30
Thanks for that.
That's how I've been doing it, I was hoping for an easier answer maybe a function or formulae embedded in excel ..... but thanks for your help

@Overtime 

With a text value such as 30.30.30 in A2, the formula

=LET(first,FIND(".",A2),second,FIND(".",A2,first+1),LEFT(A2,first-1)+MID(A2,first+1,second-first-1)/60+MID(A2,second+1,2)/3600)

will return the decimal equivalent.

The other way round, with 30.50833333 in C2, the formula

=INT(C2)&"."&INT(60*MOD(C2,1))&"."&INT(60*MOD(60*C2,1))

will return the text value 30.30.30

It would be relatively easy to modify the formulas for the format 30°30'30"

And if you prefer, you could create custom VBA functions.

You're a champion .... it works ... thankyou
Could you take it another step further?
My software outputs 30.30.30 as 30.3030 (only one decimal point)
What is a VBA function?
best response confirmed by Overtime (Copper Contributor)
Solution

@Overtime 

Are you sure that you copied and edited the formula correctly?

For cells in which you enter a value such as 30.30.3: either format the cells as text before entering data, or prefix the values with an apostrophe ' to force Excel to treat them as text.

 

I have attached a sample workbook with two VBA functions: DMS2Dec and Dec2DMS. You can view them by pressing Alt+F11.

(Remark: VBA only works in the desktop version of Excel for Windows or Mac OSX, not in Excel for Android or iOS, nor in the online/browser version of Excel)

 

The workbook is a macro-enabled workbook (.xlsm), so you'll have to allow macros when you open it.

Thanks Hans you've been very helpful
1 best response

Accepted Solutions
best response confirmed by Overtime (Copper Contributor)
Solution

@Overtime 

Are you sure that you copied and edited the formula correctly?

For cells in which you enter a value such as 30.30.3: either format the cells as text before entering data, or prefix the values with an apostrophe ' to force Excel to treat them as text.

 

I have attached a sample workbook with two VBA functions: DMS2Dec and Dec2DMS. You can view them by pressing Alt+F11.

(Remark: VBA only works in the desktop version of Excel for Windows or Mac OSX, not in Excel for Android or iOS, nor in the online/browser version of Excel)

 

The workbook is a macro-enabled workbook (.xlsm), so you'll have to allow macros when you open it.

View solution in original post