Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #2  
Old 04-22-2012, 12:22 AM
Catalin.B Catalin.B is offline Windows Vista Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, România
Posts: 386
Catalin.B is on a distinguished road
Default

It's not easy...
Let's start: assuming that in cell A1 we have : 15,332, the value of an angle formatted as decimal.
To convert this in DMS, we can do this by converting this value:
to find degrees:
=INT($A$1)
to find minutes:
=INT(($A$1-INT($A$1))*60)
to find seconds:
=(($A$1-INT($A$1))*60-INT(($A$1-INT($A$1))*60))*60
We can combine this results to show a piece of text formatted as DMS:
=INT($A$1)&"°"&INT(($A$1-INT($A$1))*60)&"'"&ROUND((($A$1-INT($A$1))*60-INT(($A$1-INT($A$1))*60))*60;2)&""""
To convert back this result 15°19'55,2" back to decimal value, this is another story...
to convert degrees:
=--(LEFT(A7; FIND("°";A7;1 ) - 1))
to convert minutes:
=--(MID(A7;FIND("°";A7;1)+1;FIND("'";A7;1)-FIND("°";A7;1)-1))/60
to convert seconds:
=--(MID(A7;FIND("'";A7;1)+1;LEN(A7)-FIND("'";A7;1)-1))/3600
Then, if you sum this 3 results, you will have the starting value in decimal value: 15,332:
=--(LEFT(A7; FIND("°";A7;1 ) - 1))+--(MID(A7;FIND("°";A7;1)+1;FIND("'";A7;1)-FIND("°";A7;1)-1))/60+--(MID(A7;FIND("'";A7;1)+1;LEN(A7)-FIND("'";A7;1)-1))/3600

You can use microsoft UDF, to do this:
Code:
Function Convert_Degree(Decimal_Deg) As Variant
    With Application
        'Set degree to Integer of Argument Passed
        degrees = Int(Decimal_Deg)
        'Set minutes to 60 times the number to the right
        'of the decimal for the variable Decimal_Deg
        minutes = (Decimal_Deg - degrees) * 60
        'Set seconds to 60 times the number to the right of the
        'decimal for the variable Minute
        seconds = Format(((minutes - Int(minutes)) * 60), "0")
        'Returns the Result of degree conversion
       '(for example, 10.46 = 10~ 27  ' 36")
        Convert_Degree = " " & degrees & "° " & Int(minutes) & "' " _
            & seconds + Chr(34)
    End With
End Function

Function Convert_Decimal(Degree_Deg As String) As Double
   ' Declare the variables to be double precision floating-point.
   Dim degrees As Double
   Dim minutes As Double
   Dim seconds As Double
   ' Set degree to value before "°" of Argument Passed.
   degrees = Val(Left(Degree_Deg, InStr(1, Degree_Deg, "°") - 1))
   ' Set minutes to the value between the "°" and the "'"
   ' of the text string for the variable Degree_Deg divided by
   ' 60. The Val function converts the text string to a number.
   minutes = Val(Mid(Degree_Deg, InStr(1, Degree_Deg, "°") + 2, _
             InStr(1, Degree_Deg, "'") - InStr(1, Degree_Deg, _
             "°") - 2)) / 60
    ' Set seconds to the number to the right of "'" that is
    ' converted to a value and then divided by 3600.
    seconds = Val(Mid(Degree_Deg, InStr(1, Degree_Deg, "'") + _
            2, Len(Degree_Deg) - InStr(1, Degree_Deg, "'") - 2)) _
            / 3600
   Convert_Decimal = degrees + minutes + seconds
End Function
but you shoud know that it has an error, the function will return 15°19'55" instead of 15°19'55,2", which is the precise conversion, and the reverse process is 15,33194 instead of 15,332
To solve this error in UDF, i would change the seconds determination in Convert_Degree to:
Code:
seconds = Format(((minutes - Int(minutes)) * 60), "#,#0.0")
and in Convert_Decimal:
Code:
seconds = Application.Substitute(Right(Degree_Deg, Len(Degree_Deg) - _
    Application.Find("'", Degree_Deg, 1)), """", "") / 3600
or:
Code:
seconds = (Mid(Degree_Deg, InStr(1, Degree_Deg, "'") + 1, _
    Len(Degree_Deg) - InStr(1, Degree_Deg, "'") - 1)) / 3600
With all this in mind ( ), you can play with your needs...
Reply With Quote
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
Date Calculation Lights Excel 5 04-18-2012 04:31 AM
calculation of start date ketanco Project 1 02-29-2012 07:01 AM
Formula calculation danbl Excel 8 02-23-2012 04:35 AM
Calculation within Cells manich1 Excel 2 12-07-2011 02:59 PM
Angle Bracket Problem, STYLEREF in Header of a Protected Document wordjunkie Word 0 06-18-2010 04:34 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:36 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft