DDD.MMSS to DDD-MM-SS

[AK_Beaver]

Hi, I'm trying to write a function that will convert an angle in the form of DDD.MMSS into one that reads DDD-MM-SS. I have most of the code worked out and I even figured it out in the excel sheet, so I'm close. I just can't seem to get it to work. I parsed the DDD part out, but I seem to be having trouble with the minute and second part. Zeros are important too. If the code is 275.091270, it needs to be broken up into 275-09-1270, with the final output looking like 275-09-13. I guess I'm having troubles with it rounding up for the seconds part too. Essentially that code needs to except any length of number for the degrees section so 1,000, 0, 9, 50, 100, 10,000, etc. If the degrees is less then 10 he final out put should have a zero preceding the degrees (i.e. 09, 05, 00, 01, etc.) I'll take any coding help, suggestions, examples, basically any help I can get. Thank you.



Function DOT2DASH(dDotAngle As Double) As String

'Function reads an angle in which is in DDD.MMSSSS format and will convert it to dash format, e.g. DDD-MM-SS.
'Written by Evan Venechuk
'03/11/2017

Dim dDot, dDegree, dminute, dSecond As Double
Dim strDMS As String

'Find position of dot

dDot = WorksheetFunction.Find(".", dDotAngle)

'Parse Dot Angle

dDegree = Left(dDotAngle, dDot - 1)
dminute = Mid(dDotAngle, dDot + 1, Len(dDotAngle) - dDot - 4)
dSecond = Right(dDotAngle, Len(dDotAngle) - (dDot + 2))

'Converts dot angle into DMS angle in dash form

strDMS = Format(dDegree, "00" & "-" & Format(dminute), "00") & "-" & Format(dSecond, "00")
'strDMS = dDegree & "-" & dminute & "-" & dSecond

'Returns Value

DOT2DASH = strDMS

End Function

Sub Test_DOT2DASH()

Debug.Print DOT2DASH("275.323320")
Debug.Print DOT2DASH("358.311836")

End Sub

[mdmackillop]

Force the text format to retain trailing 0

Function DOT2DASH(dDotAngle As Double) As String

'Function reads an angle in which is in DDD.MMSSSS format and will convert it to dash format, e.g. DDD-MM-SS.
'Written by Evan Venechuk
'03/11/2017

Dim dDot As String, dDegree As String, dminute As String, dSecond As String
Dim strDMS As String

'Find position of dot


dDot = Format(dDotAngle, "000.000000")

'Parse Dot Angle

dDegree = Split(dDot, ".")(0)
dec = Split(dDot, ".")(1)
dminute = Left(dec, 2)
dSecond = Round(Right(dec, 4) / 100, 0)

'Converts dot angle into DMS angle in dash form

strDMS = Format(dDegree, "00" & "-" & Format(dminute), "00") & "-" & Format(dSecond, "00")
'strDMS = dDegree & "-" & dminute & "-" & dSecond

'Returns Value

DOT2DASH = strDMS

End Function

[SamT]

Uhmmm...
There ain't no such thing as bearings in DDD.MMSSS notation
What looks like that is decimal degrees 132.50000 = 132 1/2o


Option Explicit


Function Dec2Dash(DecimalBearing As Variant) As String
Dim Deg As String
Dim Min 'A Variant 'Variants allows VBA to auto convert types as needed
Dim Sec 'Another Variant
Dim Tmp 'Yet Another Variant

Tmp = DecimalBearing
Deg = Left(Tmp, InStr(Tmp, ".") - 1)
Tmp = Mid(Tmp, InStr(Tmp, "."))

Min = Tmp * 60
Min = Left(Min, InStr(Min, ".") - 1)
Tmp = Tmp * 60 - Min
Min = Format(Min, "00")

Sec = Tmp * 60

Dec2Dash = Deg & "-" & Min & "-" & Sec
End Function



Sub Test_Dec2Dash()
Dim x
x = Dec2Dash(275.32332)
x = Dec2Dash(275.032332)
End Sub


BTW, in this line of yours

Dim dDot, dDegree, dminute, dSecond As Double dDot, dDegree, and dminute are all Variants

[AK_Beaver]

Actually there is such a thing as DDD.MMSSSS notation. HP35 calculators use degree notation like that. It's a land surveying thing.

[SamT]

Actually there is such a thing as DDD.MMSSSS notation. HP35 calculators use degree notation like that. It's a land surveying thing.
I didn't know that. I was in the US Navy, all we used was Integer Degrees.

So you probably can't use the Dec2Dash function. Well, keep it around, you never know.

[AK_Beaver]

Funny thing is I made a Dec2Dash the other day and one that goes dash2dec. They do come in handy, that's for sure. For some reason I can't get my Dot2Dash to work.

[SamT]

Let it be known that I could not have done this without MD's fine code to look at.


Option Explicit

Function DOT2DASH(DotAngle As Variant) As String

'Function reads an angle in which is in DDD.MMSSSS format and will convert it to dash format, e.g. DDD-MM-SS.
'Written by Evan Venechuk
'03/11/2017

Dim DD, MM, SS, Tmp

'Special cases
If DotAngle = 0 Then
DotAngle = "00.0000"
Goto FiggeringItOut
End If

If InStr(DotAngle, ".") = 0 Then
DotAngle = DotAngle & ".0000"
Goto FiggeringItOut
End If

Tmp = Len(Mid(DotAngle, InStr(DotAngle, ".") + 1))
If Tmp = 0 Then
DotAngle = DotAngle & "0000"
ElseIf Tmp = 1 Then
DotAngle = DotAngle & "000"
ElseIf Tmp = 2 Then
DotAngle = DotAngle & "00"
End If

FiggeringItOut:
Tmp = Split(DotAngle, ".")
DD = Tmp(0)
MM = Left(Tmp(1), 2)
SS = Mid(Tmp(1), 2)

DOT2DASH = Format(DD, "00") & "-" & MM & "-" & Format(SS, "00")

End Function


Sub Test_DOT2DASH()
Dim x
x = DOT2DASH(0)
x = DOT2DASH("2.")
x = DOT2DASH(275.3)
x = DOT2DASH(275.32332)
x = DOT2DASH(5.032332)
End Sub

[AK_Beaver]

SamT, when I run your code the seconds return four numbers. Any suggestions to make the out put be just two? For example, Input: 275.323320 and output: 275-32-33. I've tried working it myself and can't seem to figure it out.

[Aussiebear]

I didn't know that. I was in the US Navy, all we used was Integer Degrees.

Did you bump into anything?

[SamT]

AK
SS = Mid(Tmp(1), 3, 2)

Ted
Cupla fine shielas