MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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?

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

Title | # Comments | Views | Activity |
---|---|---|---|

create excel pivot chart | 12 | 46 | |

Excel Formula to check both condition's and return values | 2 | 35 | |

Excel VBA Macro - copy past column values only, formulas | 10 | 6 | |

Excel, contract growth per month | 2 | 6 |

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