More Efficient Method to format a date? 1

[Binnit]

I have the following piece of code which splits a string date formatted 20070101(yyyymmdd) and attempts to rebuild it in to dd/mm/yyyy 01/01/2007.

    Range("j1").Select
    ActiveCell.Value = "=Left(I3, 4)"
    
    Range("k1").Select
    ActiveCell.Value = "=Mid(I3, 5, 2)"
    
    Range("l1").Select
    ActiveCell.Value = "=Right(I3, 2)"
    
    Range("m1").Select
    ActiveCell.Value = "=L3 &  /  & K3 &  /  & j3"

The last line which trys to concatanate the cells together does not work and I would presume there is a much better solution to this anyway!.

Any guidance appreciated.

Happiness is...not getting what you want but wanting what you have already got

 

[Binnit]

Sorry - I am using Excel 97

Happiness is...not getting what you want but wanting what you have already got

 

[uberpudge]

Hello Binnit!

Assuming you really need a formula in Cells J1:M1, change the last line of your posted code to the following:
ActiveCell.Value = "=CONCATENATE(L3," & Chr(34) & "/" & Chr(34) & ",K3," & Chr(34) & "/" & Chr(34) & ",J3)"

'Chr(34) makes a " character so you can properly concatenate strings.

I might add that using ActiveCell will slow your sheet down.
If execution speed is critical, you might want to consider using the following equivalent statements to avoid ActiveCell:

Range("J1).value = "=Left(I3, 4)"

is the same thing as:

Range("j1").Select
ActiveCell.Value = "=Left(I3, 4)"


Finally, if on the other hand, you don't need a formula in your sheet for the date, you could do somthing like the following:

Dim dateWhenever As Date
dateWhenever = DateTime.DateSerial(CInt(Left(Range("I3").Value, 4)), CInt(Mid(Range("I3").Value, 5, 2)), CInt(Right(Range("I3").Value, 2)))
Range("J1").Value = Format(dateWhenever, "yyyy")
Range("K1").Value = Format(dateWhenever, "mm")
Range("L1").Value = Format(dateWhenever, "dd")
Range("M1").Value = Format(dateWhenever, "mm/dd/yyyy")


Hope this helps,
Pete

 

[CautionMP]

Binnit,
Try:
[tab][tt]Range("m1").Value = CDate(Format(Range("i3").Value, "@@@@/@@/@@"))[/tt]


Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Binnit]

uberpudge
Thanks for your thoughts, I was originally looking to have the formula in the cell but having now seen CautionMP's simple one line solution I am rethinking the need to retain the formula.

Thanks to both posts.



Happiness is...not getting what you want but wanting what you have already got