date problem

[codesmith]

hey all ...

I have a string in format:

xxxxxxxx  0007      device in xxxxxxxxxxx                    02Jul08 07:31:12

I am extracting the date portion from this string as such:

Function returnDate(statusLine As String) As String
    Dim lineDay As String
    Dim lineMonth As String
    Dim lineYear As String
    Dim rawdate As String
    
    statusLine = Trim(statusLine)
    
    rawdate = Right(statusLine, 16)
    
    lineDay = Left(rawdate, 2)
    lineMonth = Mid(rawdate, 3, 3)
    lineMonth = returnMonth(lineMonth)
    lineYear = Mid(rawdate, 6, 2)
    
    If lineMonth <= 9 Then: lineMonth = "0" & lineMonth
    
    returnDate = lineDay & "/" & lineMonth & "/20" & lineYear
    
End Function

Then writing this date to a general cell (and tried dd/mm/yyyy format cell too) as such:

    Dim lineDate As String
    wksWorking.Range("F2") = returnDate(wksNMI.Range("A4"))

This does not hold the format. It keeps changing the date to mmddyyyy (07/02/2008) ....

how would I ensure this does not happen?

Any help appreciated....

 

[dafan]

Range("F2").NumberFormat = "dd/mm/yyyy;@"

 

[Scott Huish]

I don't think you should have to do all that to get the date from that. Since you are using leading zeros on your single digit numbers, the length of the date time should not change:

Function returndate(r As String)
returndate = Left(Right(r, 16), 7)
End Function
 
Sub test()
Range("F2") = returndate(Range("A1"))
Range("F2").NumberFormat = "mm/dd/yy"
End Sub