Converting Lat/Long Coordinates from DMS to DD

[SebastianJ]

Hi, I am looking for assistance in creating a function which will convert Lat/Long coordinates from Degrees Minutes Seconds to Decimal Degrees.
The caveat is that the input coordinate is not in a traditional DMS format.
The input value is: 42.344444374, which would traditionally read N42°34'44.44374".
Respectively, -88.094498472 would traditionally read: W88°09'44.98472".

Thanks, any help is appreciated.

[MrShorty]

So, what you have, instead of DMS is you have a sort of Degree.decimal minutes or something that means whole number part + fraction part/60 -- which is what the DOLLARDE() function does (https://support.office.com/en-us/art...d-a38476693427 ). DOLLARDE(42.344444374,60) returns 42.57407 and DOLLARDE(-88.094498472,60) returns -88.157497. Are those correct?

[CK76]

DOLLARDE would be off a bit, since you need to divide minutes portion with 60, but seconds portion with 3600.

You can use following formula: Same formula applies to both Lat & Long
=(INT(ABS(A2))+ROUND(MOD(ABS(A2),1),2)*100/60+REPLACE(SUBSTITUTE(ABS(A2),LEFT(ABS(A2),FIND(".",ABS(A2))+2),""),3,0,".")/3600)*SIGN(A2)

See attached sample.

[Phuocam]

42.344444374, which would traditionally read N42°34'44.44374".
-88.094498472 would traditionally read: W88°09'44.98472".

Try this ...

=IF(A1>=0,"N","W")&SUBSTITUTE(TEXT(ABS(A1),"00.00\'00\.0000\'\'"),".","°",1)

[MrShorty]

DOLLARDE would be off a bit, since you need to divide minutes portion with 60, but seconds portion with 3600.

You are correct. I misread the problem statement and thought he had decimal minutes in the fraction portion of the number.

[SebastianJ]

Yes, Thank you CK76.
This is what I was looking for.

[SebastianJ]

Generally, I have had success with the below function.
However, I've notice that a few conversions in each data set I apply this to, does not return the correct converted value.

=(INT(ABS(A2))+ROUND(MOD(ABS(A2),1),2)*100/60+REPLACE(SUBSTITUTE(ABS(A2),LEFT(ABS(A2),FIND(".",ABS(A2))+2),""),3,0,".")/3600)*SIGN(A2)

For example:

Input:
42.34503058 -88.09529052

Return:
42.59730716 -88.18136256

Where as the correct DD conversion is:
42.58064049444444, -88.16469589722222

Any thoughts?

Thanks

[CK76]

That's probably due to ROUND function for minute part.

Try...
=(INT(ABS(A2))+LEFT(MOD(ABS(A2),1)*100,2)/60+REPLACE(SUBSTITUTE(ABS(A2),LEFT(ABS(A2),FIND(".",ABS(A2))+2),""),3,0,".")/3600)*SIGN(A2)