Excel 2007 help

Discussion in 'OT Technology' started by bigboostdsm, Aug 27, 2008.

  1. bigboostdsm

    bigboostdsm New Member

    Joined:
    May 19, 2006
    Messages:
    7,989
    Likes Received:
    0
    Location:
    Rocky Top, TN
    Hey, I need some help figuring out if excel can do what I want it to.

    I need to take a number in Degree, Minutes, Seconds and convert it to a decimal.

    Ex -

    I need to take something like 9°05'00" and make it 9.08333 without any extra input besides the DMS.

    Does excel 07 even have such a thing? I couldn't find a formula in the list, but maybe I overlooked something :o
     
  2. bovine

    bovine New Member

    Joined:
    Jan 3, 2006
    Messages:
    2,883
    Likes Received:
    0
    Location:
    Austin, TX
    Google DMS excel conversion. You'll need to copy and paste a VB script to do that.
     
  3. bigboostdsm

    bigboostdsm New Member

    Joined:
    May 19, 2006
    Messages:
    7,989
    Likes Received:
    0
    Location:
    Rocky Top, TN
    Yep, a guy in my class made a script for it last year and just gave it to me. Thanks though.

    DMS has to be in d,mm,ss format and it changes it right over.

    Code:
    [FONT=Calibri][SIZE=3][COLOR=#000000]Sub degrees()[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]If ActiveCell.Value <> "" Then[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]x = 1[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]length = Len(ActiveCell.Value)[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]d = ""[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]minutes = ""[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]seconds = ""[/COLOR][/SIZE][/FONT]
     
    [FONT=Calibri][SIZE=3][COLOR=#000000]Do[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]spot = Mid(ActiveCell.Value, x, 1)[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]x = x + 1[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]If spot <> "," Then d = Trim(d + spot)[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]Loop Until spot = "," Or x = length[/COLOR][/SIZE][/FONT]
     
    [FONT=Calibri][SIZE=3][COLOR=#000000]Do[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]spot2 = Mid(ActiveCell.Value, x, 1)[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]x = x + 1[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]If spot2 <> "," Then minutes = Trim(minutes + spot2)[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]Loop Until spot2 = "," Or x = length[/COLOR][/SIZE][/FONT]
     
    [FONT=Calibri][SIZE=3][COLOR=#000000]Do Until x = length + 1[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]spot3 = Mid(ActiveCell.Value, x, 1)[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]x = x + 1[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]seconds = Trim(seconds + spot3)[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]Loop[/COLOR][/SIZE][/FONT]
     
    [FONT=Calibri][SIZE=3][COLOR=#000000]d = Val(d)[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]minutes = Val(minutes) / 60[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]seconds = Val(seconds) / 3600[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]Cells(ActiveCell.Row, ActiveCell.Column + 1).Value = d + minutes + seconds[/COLOR][/SIZE][/FONT]
    [FONT=Calibri][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]
     
    [FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]
    
     
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    It can be done if the DMS is entered exactly how you've put it - or any way really. You just need to separate out the 3 elements and then:

    First element + second element/60 + third element/3600
     
  5. bigboostdsm

    bigboostdsm New Member

    Joined:
    May 19, 2006
    Messages:
    7,989
    Likes Received:
    0
    Location:
    Rocky Top, TN
    Then each piece of the DMS would need to be in it's own column right?

    say degrees in d8, mins in d9, and sec in d10. Then d8 + d9/60 + d10/3600. But then I've got a bunch of columns I'd rather not have, I guess it will work if I can't get that code to work right. Thanks.
     
  6. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    No - you can have it all in one column, but the separator (ie the degree sign, ' and the ") have to be consitant. If you end up with different data layouts then it's harder to extract the 3 different elements. You can use the instr function to find where each delimiter is in the string.
     
  7. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    my TI-89 can do this!
     
  8. bigboostdsm

    bigboostdsm New Member

    Joined:
    May 19, 2006
    Messages:
    7,989
    Likes Received:
    0
    Location:
    Rocky Top, TN
    So can my TI-83, but I need it in a spreadsheet to solve area by DMD.
     
  9. bovine

    bovine New Member

    Joined:
    Jan 3, 2006
    Messages:
    2,883
    Likes Received:
    0
    Location:
    Austin, TX
    My $15 Casio can do it too, but I don't think that was the point.
     
  10. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    mah bad
     
  11. bigboostdsm

    bigboostdsm New Member

    Joined:
    May 19, 2006
    Messages:
    7,989
    Likes Received:
    0
    Location:
    Rocky Top, TN
    :werd:

    The idea is that I'm creating a spreadsheet where I can take bearing angles and distances from a survey that I perform and input the angle in DMS and the length between the angles in feet; then calculate latitude, departure, corrected latitude, corrected departure, DMD, area in feet, area in acres, and coordinates (given a coordinate for the first benchmark).

    When all said and done I'll need to input two numbers for each point in a closed traverse and the spreadsheet will do all the work from there.
     
    Last edited: Aug 28, 2008

Share This Page