CONVERTING DECIMAL DEGREES TO DMS IN EXCEL TO TWO PLACES (EXAMPLE 10?ø47’25.32″) - Strictly Surveying

Strictly Surveying

CONVERTING DECIMAL DEGREES TO DMS IN EXCEL TO TWO PLACES (EXAMPLE 10?ø47’25.32″)

Posted by ekillo on July 3, 2019 at 11:25 pm

I need a little help with this. What do I need to change to get two places on the seconds?

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 Minute
Seconds = Format(((Minutes – Int(Minutes)) * 60), “0”)
‘Returns the Result of degree conversion
‘(for example, 10.46 = 10~ 27 ‘ 36″)
Convert_Degree = ” ” & Degrees & “?ø ” & Int(Minutes) & “‘ ” _
& Seconds + Chr(34)
End With
End Function

Thanks,

Ed

ekillo replied 5 years, 3 months ago 5 Members · 12 Replies
12 Replies

field-dog

Member
July 3, 2019 at 11:54 pm

Looks like you got the code from https://support.microsoft.com/en-us/help/213449/how-to-convert-degrees-minutes-seconds-angles-to-or-from-decimal-angle.

I’ll try to figure this out for you just because it’s a challenge.

MH
field-dog

Member
July 4, 2019 at 12:02 am

https://excel.tips.net/T003085_Formatting_for_Hundredths_of_Seconds.html

MH
field-dog

Member
July 4, 2019 at 12:18 am

https://www.excelsupersite.com/easily-convert-decimal-degrees-to-degrees-minutes-seconds-in-excel/

This looks like it addresses your issue.

MH
ekillo

Member
July 4, 2019 at 2:14 am

Thanks.

I have never understood why this is not a built in function in EXCEL.

Ed
jimcox

Member
July 4, 2019 at 7:00 am

Because excel, like most computers, thinks in Radians…

BTW I have a little add on for excel, that gives you the hms, r->p, etc functionality of the old HP calculators. Free for the asking…
ekillo

Member
July 4, 2019 at 7:27 am

Jim,

I would like to get a copy of your program, I am working on a project for the next few months that I will be having to convert several hundred points.

Thanks,

Ed
mathteacher

Member
July 4, 2019 at 4:01 pm

You probably won’t encounter any negative angles, but, if you do, the INT function will lead you astray. While INT(10.6) = 10, INT(-10.6) = -11. That’s because the integer function selects the next lowest integer, and -11 is lower than -10.

Using the truncate function solves that problem.
field-dog

Member
July 4, 2019 at 4:15 pm

I’d like it, please.

MH
loyal

Member
July 4, 2019 at 4:27 pm

Posted by: MathTeacher

You probably won’t encounter any negative angles, but, if you do, the INT function will lead you astray. While INT(10.6) = 10, INT(-10.6) = -11. That’s because the integer function selects the next lowest integer, and -11 is lower than -10.

Using the truncate function solves that problem.

I know NOTHING about EXCELL (or any other such program, I never had any use for them), but wouldn’t a negative Longitude (North America for example) be an issue as well?

Loyal
field-dog

Member
July 4, 2019 at 4:29 pm

What is the source of your several hundred points in decimal degrees? Maybe Excel isn’t the best solution for you.

MH
mathteacher

Member
July 4, 2019 at 5:37 pm

Yes, as would negative convergence angles, which is where I was reunited with the problem. Back in my old Fortran days, we had a rounding subroutine from IBM called ROCSP. Lo and behold, it couldn’t consistently round negative numbers correctly, so we wrote our own.

We also have to be careful about adding and subtracting decimal parts in these conversions as well.
ekillo

Member
July 5, 2019 at 2:41 am

You are probably right, but I was sent an Excell file with a list of points in decimal degrees that I needed to locate, I know I could have gotten by not converting them, but I am old school and think in DMS.