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)*60
You 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)*60
Again, 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 angles
The 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?
You can use a custom format like [h]º mm' ss.0" to display correctly (without the colons) - but you still have to use colons when entering the values. And since Excel treats them as time, it's a PITA to edit in the cell - the formula bar displays it as a time in the first few days of 1900. (I just re-enter the whole value.) It does work for values over 90 degrees, but I don't think it will handle negative values at all.
The advantage of using Excel's time formats: converting DMS to decimal degrees is just multiplying by 24. (And it's all contained in one cell.)
Excel spread sheet degrees and sexagesimal degrees
handyman6047, there are problems with negative angles using your formula. Try, for example, -25.3542 deg.
My solution for converting from degrees to dd°mm'ss" is as follows:
Degrees: TEXT(IF(B8=0,0,(B8/ABS(B8))*INT(ABS(B8))),"00°")
Minutes: TEXT(INT(MOD((ABS(B8)*60),60)),"00'")
Seconds: TEXT(MOD((ABS(B8)*3600),60),"00.0")&""""
Completed:
=TEXT(IF(B8=0,0,(B8/ABS(B8))*INT(ABS(B8))),"00°")
&TEXT(INT(MOD((ABS(B8)*60),60)),"00'")
&TEXT(MOD((ABS(B8)*3600),60),"00.0")&""""
Above three lines should be combined into one. The formula is presented this way because of Beerleg input "Too long of a word" limitations.
Note:
This works on an old Macintosh. There may be some problems with the degree, minute and seconds characters on non-Mac systems.
Degree symbol = °
Minute symbol = '
Second symbol = "
Rounding above is to the nearest 0.1" (Notice the "00.0" as the second parameter in the TEXT function). Rounding to the nearest second may result in errors when seconds are in the range 59.5 to 60.
My personal preference is the Hewlett-Packard calculator format dd.mmss, not dd°mm'ss" Changes to the Excel formula, above, will produce desired results.
The cell I referenced is B8. Change appropriately.
I agree with Greg S in that working with the time functions is a pain ...!
I have to run out for a few minutes, but later I will post a link to functions I have created for Excel which allow you to convert both ways with formatting.
I should note that the formulas I shared were "lifted" from other sources. I'm just a babe in the woods when it comes to Excel. Slowly learning the benefits of Excel to surveying.
Negative angles? What's that?;-)
I am not familiar with Excel formulas, so I could be wrong, but shouldn't you add something like 1/7200 of a degree before the operation starts to account for a proper rounding to the nearest second (or 1/7200O) if the display is to a tenth of a second.
You don't really need all these functions and special programming. It is really not all that bad to have D M and S in their own columns and then apply simple math.
What is the fricking problen? I have all kinds of spreadsheets that work fine without functions.
Geez...
The first link below is for an Excel 2003 worksheet that has Visual Basic Functions built-in for converting decimal degrees to DMS formatted (xx°xx'xx.x") and DMS formatted (dd.mmss) to decimal degrees. I also left my test data in the worksheet so that you can see how it's supposed to work.
The second link contains the Visual Basic code for the two functions. This will allow you to import the functions into existing worksheets. To import, go to "Tools", "Macros", "Visual Basic Editor". Once the Visual Basic Editor opens, go to "File", "Import File" and navigate to the .bas file obtained from the second link.
I originally wrote these functions in college for a traverse calculation sheet that allowed me to enter bearings and distances and output closure error, precision, corrected bearings & distances, and area. The original functions required the DMS data be entered in the format (dd,mm,ss), assumed positive angles, and assumed that minutes and seconds each would be less than 60. I have beefed them up to handle negative angles and to return "0" or exit with no result if the input data is faulty.
Usage is simple once the functions are loaded into your worksheet. Entering =DMS(A1) into a cell will convert the value contained in cell "A1" to DMS formatted (xx°xx'xx.x"). The result will display in the cell where formula is entered. Entering =DDeg(A1) will convert the contents of cell "A1" to decimal degrees. This function requires the data in the target cell to be in the format (dd.mmss). DMS returns seconds to one decimal place but is easy to modify for more or less digits. DDeg returns decimal degrees to as many places as Excel will carry, round or truncate by normal methods.
Why work so hard?
Create a custom cell format.
Type in the numbers and go.
Scott,
Thanks for the screen shot. I never realized that you could take such liberties with the custom formatting.
The point of creating the functions was for ease of calculations when converting from DMS to decimal degrees or decimal degrees to DMS. Outputting the data in a recognizable format was just the icing on the cake.
One word-grads. That is what I use to process and adjust. So much simpler. But, like the metric system, it seems to be unusable by US surveyors.
Thanks for that
- any chance of reposting the formulas (the .bas file).
The links are down I'm afraid.
I do the same.
Could you reshare or send me the excel files for this? Thanks!!!!:-)
The latest versions of Excel don't like how I programed the routine to handle negative numbers. I'll beat it into submission later today and post new links.
Thanks!!!!!!:-)
> I have to run out for a few minutes, but later I will post a link to functions I have created for Excel which allow you to convert both ways with formatting.
Excellent - are these custom functions you can load into Excel?
I was going to do this myself.
I was also thinking of functions for simple co-ordinate join (inverse) and vice versa.
They must be out there.
oops didn;t noticed this was from years ago.
It was even weirder seeing myself replying further down the page, that was a bit dejavu.
But that's not a number you can use without converting. I mean you can't put that into the SIN formula for example.
The first link below is for an Excel 2003 worksheet that has Visual Basic Functions built-in for converting decimal degrees to DMS formatted (xx°xx'xx.x") and DMS formatted (dd.mmss) to decimal degrees. I also left my test data in the worksheet so that you can see how it's supposed to work.
The second link contains the Visual Basic code for the two functions. This will allow you to import the functions into existing worksheets. To import, go to "Tools", "Macros", "Visual Basic Editor". Once the Visual Basic Editor opens, go to "File", "Import File" and navigate to the .bas file obtained from the second link.
I originally wrote these functions in college for a traverse calculation sheet that allowed me to enter bearings and distances and output closure error, precision, corrected bearings & distances, and area. The original functions required the DMS data be entered in the format (dd,mm,ss), assumed positive angles, and assumed that minutes and seconds each would be less than 60. I have beefed them up to handle negative angles and to return "0" or exit with no result if the input data is faulty.
Usage is simple once the functions are loaded into your worksheet. Entering =DMS(A1) into a cell will convert the value contained in cell "A1" to DMS formatted (xx°xx'xx.x"). The result will display in the cell where formula is entered. Entering =DDeg(A1) will convert the contents of cell "A1" to decimal degrees. This function requires the data in the target cell to be in the format (dd.mmss). DMS returns seconds to one decimal place but is easy to modify for more or less digits. DDeg returns decimal degrees to as many places as Excel will carry, you may round or truncate by normal methods.
My programing is rough and ugly and I'm years removed from when I originally wrote this code so newer versions of Excel didn't care for the liberties I took when writing the code for the negative numbers. I've reworked these to work in Excel 2013, I think they should still work in older versions as well.
