Best way to convert Degrees Minutes and Seconds to Decimal of Degrees (and Vice Versa)

I have found the following excel function code on the internet:

Please Login or Register  to view this content.

The above function code requires the manual (and tedious) input of the degree symbol "º" pasted into every cell for the angular value.

I have cell values custom formatted using Custom Type: [h]º mm' ss.0\" to represent the angular values the user inputs (trying to remove the need to do the previous sentence).

This obviously does not work as the actual cell values are in units of time.

How do I get the above function code to use the [h]º mm' ss.0\" cell value?

As the cell input is in degrees, minutes and seconds, I do not see how I can simply multiply the value by 24 (in the function code) as the input value is not decimal degrees (thus, conversion won't hold true).

Any ideas?

Thanks,

Aussie_Student

I understand Ford's post as accepting answers to the thread again (indeed there are problems with editing today)

So I'm posting what I had ready, but not sent, because I noticed AliGW post

---

Two comments
- merit one:
If I understand you well you have in a cell 15:05:13 displayed (formatted) as 15º 05' 13.0" - then indeed you can simply multiply value by 24

Please Login or Register  to view this content.

Try it. It's working as seen on the screenshot attached
- administrative: please edit your post and use code tags, not just italics for the code it improves readability and gives a tool to easy copy all code - moreover it is formal requirement here: Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between

Please Login or Register  to view this content.

tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here: https://www.excelforum.com/forum-rul...rum-rules.html

PS. note that function argument is no longer String, but Date now.

(It's separate post, because again trying editing, I got empty message box)

As for vice versa - yes, you can just divide by 24, store as date/time and use custom formatting

Hi Kaper,

Thank you for the PM.

Just before I received the updates and your PM I tried and confirmed that if I formatted another cell as general, referenced the DMS value and divided it by 24 it converted the value in to DD.

The post is now in the proper format.

Thank you again and I will use the code descriptor from now on.

Aussie_Student

Indeed it was so in the morning, but Just few seconds ago I edited another post and it worked. Hope it was just temporary problem.