New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Solved

Posted on 2016-09-13

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?

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

- Help others & share knowledge
- Earn cash & points
- Learn & ask questions

2 Comments

Then change the TEXT function for the seconds part to output the values as 00.0.

Here is the updated function.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Course of the Month8 days, 7 hours left to enroll

Join the community of 500,000 technology professionals and ask your questions.