Issues with Converting from DMS to DD

[mpratt]

I have a couple hundred Lat/Long coordinates to convert to decimal degree in this DMS format:

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]Latitude[/TD]
[TD]Longitude[/TD]
[/TR]
[TR]
[TD]37° 34' 45.90"[/TD]
[TD]-87° 39' 34.60"[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I've tried various VBA codes via help sites and a few equations but nothing I've tried has worked. I'm not the most adept at Excel, which may be part of the problem, but I simply don't know what else to do to convert these coordinates.

I'd appreciate any help at all.

Thanks!
-Maddy

 

[Andrew Poulsom]

How can you have 88.7 and 85.7 seconds?

 

[mpratt]

The data in this document is not mine, someone else collected it. These are the values of the Lat/Long that I was provided. Also, these coordinates were taken in 1999. I have coordinates like this all the way up to 6/2/2014. I've been working with what I was given. So to answer, I don't know how/why my supervisor got 88.7" and 85.7" for that particular point.

*Edit:
But if that is what is causing all of these issues, then I need to take it up with my supervisor.

 

[Rick Rothstein]

That worked for most of the longitude values. I'm still getting an error message on a few of the cells however. I'm not sure what the error is though. For the cells with the error message this is what I know:

-Error Message for Long cells:

• Show Calculation steps > 24*TEXT(ABS(K13), "0\:00\:00.0")*Sign(K13) > Evaluate > 24*TEXT(ABS(-874685.7), "0\:00\:00.0")*Sign(K13) > Evaluate> 24*TEXT(874685.7, "0\:00\:00.0")*Sign(K13) >Evaluate> 24*"87:46:85.7"*Sign(K13) >Evaluate > #Value!*Sign(K13) >Evaluate> #Value!*Sign(-874685.7) >Evaluate> #Value!*-1 >Evaluate > #Value!

-Error Message for Lat cells:

• Show Calculation steps> 24*TEXT(J13), 0\:00\:00.0")> Evaluate> 24*TEXT(371788.7,"0\:00\:00.0") > Evaluate > 24*"37:17:88.7" (The next evaluation will show an error) > Evaluate > #Value!

-Formatting for cells: Custom- 00° 00' 00.00''
-Formula bar display for the cell: 371788.7 for latitude and -874685.7 for longitude

I'm not sure if this is helpful at all, but I don't understand what Excel is telling me is wrong.

I think the problem is we are trying to use a time function on a value whose time would be greater than 24 hours. Assuming your values always include tenths of a second, see if this formula works for you...

=LEFT(A1,LEN(A1)-6)+24*SIGN(A1)*RIGHT(TEXT(ABS(A1), "0\:00\:00.0"),7)

 

[mpratt]

I think the problem is we are trying to use a time function on a value whose time would be greater than 24 hours. See if this formula works for you...

=LEFT(A1,LEN(A1)-6)+24*SIGN(A1)*RIGHT(TEXT(ABS(A1), "0\:00\:00.0"),7)

That fixed some of the cells, but there are 3 that are showing an error that weren't before.

 

[Andrew Poulsom]

I think the problem is we are trying to use a time function on a value whose time would be greater than 24 hours. See if this formula works for you...

=LEFT(A1,LEN(A1)-6)+24*SIGN(A1)*RIGHT(TEXT(ABS(A1), "0\:00\:00.0"),7)

Your formula works, but the problem was with the number of seconds. The original formula works for -874655.7 but not for -874685.7.

 

[mpratt]

One of the cells didn't have any Latitude coordinate to use, that is why it is showing an error. But the other two do have Lat coordinate inputs. However, they have 21.00" and 45.00" I'm assuming the ".00" is causing the problem?

 

[mpratt]

Your formula works, but the problem was with the number of seconds. The original formula works for -874655.7 but not for -874685.7.

How so? I mean, why does it work for one but not the other?

 

[Andrew Poulsom]

For some reason "87:46:85.7" isn't coerced into a number like "87:46:85.7" is.

="87:46:85.7"+0 #VALUE!
="87:46:55.7"+0 3.65758912037037

 

[mpratt]

I think the problem is we are trying to use a time function on a value whose time would be greater than 24 hours. Assuming your values always include tenths of a second, see if this formula works for you...

=LEFT(A1,LEN(A1)-6)+24*SIGN(A1)*RIGHT(TEXT(ABS(A1), "0\:00\:00.0"),7)

Well Mr. Rothstein said it might have to do with the fact that there can't be 85 seconds as a time measurement. I used this equation he provided and it fixed that problem with most of my Lat. coordinates. But I'm still having issues with the Long. coordinates as well.

 

[Rick Rothstein]

For some reason "87:46:85.7" isn't coerced into a number like "87:46:85.7" is.

="87:46:85.7"+0 #VALUE!
="87:46:55.7"+0 3.65758912037037

I still think exceeding 24 hours plays some kind of role... change the 87 hours to any positive value less than 24 (leaving the 85.7 seconds) and it will return a value instead of an error. Probably has to do with why we need to put square brackets around the hour part of a format pattern to make it work with hours greater than 24.