Date Formatting (e.g. 050914) into excel formula

[killpaddy]

Hi,

How would you get a vba defined string e.g. "050914" to be ouput in a cell as "05/09/2014" (or just "05/09/14")

It must be classed as an actual date, not just a string in the cell for sorting and ordering etc.

Thanks in advance.

 

[steve the fish]

Try this:

=LEFT(A1,2)&"/"&MID(A1,3,2)&"/20"&RIGHT(A1,2)

 

[killpaddy]

Thanks, I used your formula as a basis, but I had to execute it in VBA, not excel formulas. All the extra functions, such as Trim() and Str() were needed to get the string formatting correct. Chr(34) is the speech marks (") character code which is needed to write " into the string when coding a formula input in vba.

I formatted the destination cells as a Date (*dd/mm/yyyy) manually and it worked.

DateStr = "090914"
With Range("A1") 
    .formula = "=DATEVALUE(" & Chr(34) & Trim(Str(left(DateStr, 2))) & "/" & _
        Trim(Str(Mid(DateStr, 3, 2))) & "/20" & Trim(Str(Right(DateStr, 2))) & Chr(34) & ")"
End With

Output: '=DATEVALUE("09/09/2014")'