trevor1940

asked on

# Converting Decimal Degrees to D:M:S in excel Part 2

This is a follow on qquestion from Converting Decimal Degrees to D:M:S in excel

There is a rounding error in The accepted solution

Given these values in A2 & B2

78.004 123.997 I get this 78:00:00:N/123:60:00:E

78.004 should be 78:00:14.4

123.997 should be 123:59:49.2

78:00:14:N/123:59:49:E

Any ideas how I amend the formula?

There is a rounding error in The accepted solution

`=CONCATENATE(TEXT(INT(ABS(A2)),"000"),":",TEXT(INT(ROUND(MOD(ABS(A2),1),2)*60),"00"),":",TEXT(ROUND(MOD(ROUND(MOD(ABS(A2),1),2)*60,1),2)*60,"00"),":",IF(A2<0,"S","N"),"/",TEXT(INT(ABS(J2)),"000"),":",TEXT(INT(ROUND(MOD(ABS(J2),1),2)*60),"00"),":",TEXT(ROUND(MOD(ROUND(MOD(ABS(J2),1),2)*60,1),2)*60,"00"),":",IF(J2<0,"W","E"))`

Given these values in A2 & B2

78.004 123.997 I get this 78:00:00:N/123:60:00:E

78.004 should be 78:00:14.4

123.997 should be 123:59:49.2

78:00:14:N/123:59:49:E

Any ideas how I amend the formula?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER