Formula to convert Decimal Degrees to Degrees, Minutes, Seconds

[bot...@monad.net]

Surprisingly I havent found a built in Formaula or cell formatthat allows you to convert a cell value from ''decimal degrees''into ''degrees minutes and seconds''EX: if I have a formula in a cell that returns ''1.99'' and I want to display that value in Degrees Minutes and Seconds equivilant''1:59:24''Any help would be appreciatedThanks Doug Bottom

[John Green]

In article <34a7f...@news.infowest.com> on 29 Dec 97 19:30:46 GMT

bot...@monad.net wrote:
<< Surprisingly I havent found a built in Formaula or cell formatthat
allows you to convert a cell value from ''decimal degrees''into
''degrees minutes and seconds''EX: if I have a formula in a cell that
returns ''1.99'' and I want to display that value in Degrees Minutes and
Seconds equivilant''1:59:24'' >>

Hi Doug,

Here is a quick and dirty way to do it. As Degrees/Minutes/Seconds
follow the same pattern as Hours/Minutes/Seconds, you can divide your
decimal degress by 24 and format the cell as [h]:mm:ss

If 1.99 is in A1, enter

=A1/24

and apply the custom cell format to the formula cell,

Hope this helps,

John Green - Excel MVP
Sydney
Australia

[George Simms]

Hi Doug.
I have two Custom Functions to do that. Copy them to a module sheet.
To use =DegMinSec(A1) will convert decimal to ''degrees minutes and
seconds'' & =DegDecimal(A2) will convert it back.

'Returns Deg. º Minute ' seconds"
Function DegMinSec(Degrees) As String
Dim Deg As Integer, Sec As Double, Neg As Boolean
If Degrees < 0 Then
Degrees = -Degrees
Neg = True
End If
Deg = Int(Degrees)
Sec = (Degrees - Deg) * 3600
Min = Int(Sec / 60)
Sec = Sec - (Min * 60)
Sec = Application.Round(Sec, 3)
DegMinSec = Str(Deg) & Chr(186) & Str(Min) & "'" & Str(Sec) &
Chr(34)
If Neg = True Then
DegMinSec = "-" & Right(DegMinSec, Len(DegMinSec) - 1)
End If
End Function

Function DegDecimal(DegMinSec) As Single
If Left(DecMinSec, 1) = "=" Then
Sign = -1
Else
Sign = 1
End If
DegPos = InStr(DegMinSec, Chr(186))
Deg = Val(Left(DegMinSec, DegPos - 1))
PrimePos = InStr(DegMinSec, "'")
Min = Val(Mid(DegMinSec, DegPos + 2, PrimePos - DegPos - 2)) * Sign
DblPrimePos = InStr(DegMinSec, Chr(34))
Sec = Val(Mid(DegMinSec, PrimePos + 2, DblPrimePos - PrimePos -
0.2)) * Sign
DegDecimal = Deg + Min / 60 + Sec / 3600
End Function

Using John's "quick and dirty way to do it." (it still works), Format the
answer cell to [h]"º "mm' ss\" put this in the "Type:" box .To get the
º on the * numeric key * pad holding down Alt type 0186 . This will
give the answer as 45º 47' 20" .

--
All The Best

George

Newcastle Upon Tyne
England