Mar 15 2022 03:13 AM
Mar 15 2022 03:27 AM
Mar 15 2022 03:33 AM
Mar 15 2022 03:42 AM
Mar 15 2022 04:16 AM
With a text value such as 30.30.30 in A2, the formula
will return the decimal equivalent.
The other way round, with 30.50833333 in C2, the formula
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.
Mar 15 2022 05:17 AMSolution
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.