# Decimal seconds, please.

June 10, 2008 2:03 PM Subscribe

Microsoft Excel VBA question: Converting decimal degrees to degrees-minutes-seconds (DMS), with rounding customization.

I'm pretty proficient with Excel but am a complete n00b when it comes to programming even Visual Basic. I needed to convert some decimal degrees into DMS format, and failing to find a function that did so, I went to Google and found this page. Surprisingly enough, both of those modules worked successfully, allowing me to access them through the Function menu. I was even able to figure out how to change the function name to something I find clearer.

My one gripe about the Decimal-to-DMS conversion is that it doesn't allow for any numbers after the decimal point in the Seconds part of the output. I wouldn't usually mind (I know how tiny a second is), but I'm using this for a mapping application where meters matter, and the rounding is throwing me off. Can the hive mind help me get decimal seconds?

I'm pretty proficient with Excel but am a complete n00b when it comes to programming even Visual Basic. I needed to convert some decimal degrees into DMS format, and failing to find a function that did so, I went to Google and found this page. Surprisingly enough, both of those modules worked successfully, allowing me to access them through the Function menu. I was even able to figure out how to change the function name to something I find clearer.

My one gripe about the Decimal-to-DMS conversion is that it doesn't allow for any numbers after the decimal point in the Seconds part of the output. I wouldn't usually mind (I know how tiny a second is), but I'm using this for a mapping application where meters matter, and the rounding is throwing me off. Can the hive mind help me get decimal seconds?

Hah... was their bug not mine. In fact, I'm amazed their function works at all. That '+' on the third last line should be a '&' as far as I can tell.

So, anyway, the correct answer for arbitrary precision (up to the limit of the data type) is:

(with changed lines in bold)

posted by pompomtom at 7:10 PM on June 10, 2008

So, anyway, the correct answer for arbitrary precision (up to the limit of the data type) is:

(with changed lines in bold)

Function Convert_Degree(Decimal_Deg) As Variant With Application 'Set degree to Integer of Argument Passed degrees = Int(Decimal_Deg) 'Set minutes to 60 times the number to the right 'of the decimal for the variable Decimal_Deg minutes = (Decimal_Deg - degrees) * 60 'Set seconds to 60 times the number to the right of the 'decimal for the variable Minuteseconds = ((minutes - Int(minutes)) * 60)'Returns the Result of degree conversion '(for example, 10.46 = 10~ 27 ' 36") Convert_Degree = " " & degrees & "° " & Int(minutes) & "' " _& seconds & "End With End Function

posted by pompomtom at 7:10 PM on June 10, 2008

erm.. that didn't quite work. the last double quotes should be the chr() code as in the example (or simply four double quotes in a row will achieve the same).

posted by pompomtom at 7:13 PM on June 10, 2008

posted by pompomtom at 7:13 PM on June 10, 2008

Response by poster: Oh, OK. When I made the change you suggested, the function worked for some old instances of it I had on the sheet, but when I tried to make new ones it didn't. Weird. Thanks again!

posted by dondiego87 at 2:10 PM on June 12, 2008

posted by dondiego87 at 2:10 PM on June 12, 2008

This thread is closed to new comments.

Seconds = Format(((Minutes - Int(Minutes)) * 60), "0")

to:

Seconds = Format(((Minutes - Int(Minutes)) * 60), "0.

00000")(my emph...)

That'll give you five decimal places of seconds.

posted by pompomtom at 4:07 PM on June 10, 2008