Excel formulas for DMS to D.dddd and D.dddd to DMS

handyman6047 · 2011-09-30T16:08:35Z

An Excel formula to convert decicmal degree to DMS with character separators is:=INT(A?)&CHAR(46)&INT((A?-INT(A?))*60)& ROUND((((+A?-INT(A?))*60)-INT((+A?-INT(A3))*60)),2)*60You must replace "A?" with the correct cell designation. Note that a space was added to the formula after the "&""ROUND". I received a warning when I previewed the post that the formula (the word) as too long.To create the value without the characters, the following formula can be used:=INT(A?)&CHAR(46)&INT((A?-INT(A?))*60)& ROUND((((+A?-INT(A?))*60)-INT((+A?-INT(A?))*60)),2)*60Again, the correct cell designation must be used. Note again that a space was added to the formula after the "&" and before "ROUND". I received a warning when I previewed the post that the formula (the word) as too long.Unfortunately this represents the tenths of a second and following figures with a point separator and not just as the next digit(s) after the Ones place of the seconds value. Any suggestions to modify?- - - - - - - - - - - - - - - -After searching for some time on the internet, there does not seem to be an easy Excel formula to convert DMS to d.dddd The use of the "Time" number format [h]:mm:ss is one answer but I believe does not like values over 90 for the degrees. Also, the number must be represented with the colon separators - not the normal surveyor representation of anglesThe formula:={1,0,0}*INT(A?)+{0,1,0}*MINUTE(A?/24)+{0,0,1}*SECOND(A?/24)can be used but the degrees minutes seconds are separated into separate columns with each column re-using the same formula.Any suggestions?

Summarize Topic

Page 2 / 2 Prev

Strictly Surveying

Last Post by shadow7 12 years ago

22 Posts

12 Users

0 Reactions

4,276 Views

RSS

strizzy

(@strizzy)

Posts: 55

Member

Translate ▼

Not sure if this helps, but I made this for one of the first labs I did in school, I love Excel...go Compass Rule... It only has DMS to d.ddd, but its easy!

Can't seem to upload the xls file, but I have it if you want...

Posted : May 23, 2014 2:36 pm

shadow7

(@shadow7)

Posts: 3

Member

Translate ▼

Thanks!!!!!!!!🙂

Posted : May 23, 2014 9:15 pm

Page 2 / 2 Prev

26 Forums
54.5 K Topics
592.1 K Posts
265 Online
16.9 K Members

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed

Excel formulas for DMS to D.dddd and D.dddd to DMS

Please support our supporters!

Our Policies

Quick Links

Support