![]() |
#2
|
|||
|
|||
![]()
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 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") Code:
seconds = Application.Substitute(Right(Degree_Deg, Len(Degree_Deg) - _ Application.Find("'", Degree_Deg, 1)), """", "") / 3600 Code:
seconds = (Mid(Degree_Deg, InStr(1, Degree_Deg, "'") + 1, _ Len(Degree_Deg) - InStr(1, Degree_Deg, "'") - 1)) / 3600 ![]() |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
Lights | Excel | 5 | 04-18-2012 04:31 AM |
calculation of start date | ketanco | Project | 1 | 02-29-2012 07:01 AM |
![]() |
danbl | Excel | 8 | 02-23-2012 04:35 AM |
![]() |
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 |