VBA Date String Issue

[playern07]

I have a string of dates I am trying to concatenate. I would like to take a date and put it in a particular format, but for some reason my code only puts the 1st date in such format and the rest of them in the arbitrary formatt. Any ideas?

Sheet1.Select
Dim DateQRY As String
Dim cell2 As Range
DateQRY = Sheet1.Range("$B$2")
DateQRY = Format(DateQRY, "'yyyy-mm-dd'")
For Each cell1 In Range("OFFSET($B$3,0,0,COUNT($B:$B)-1,1)").Cells
DateQRY = DateQRY & "," & cell1.Value
Next

Sample Output: DateQRY = ('2011-07-07',7/0/2010,7/23/2011,...)

 

[VoG]

Try

Sub DateTest()
Dim LR As Long, i As Long, s As String
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    s = s & Format(Range("B" & i).Value, "yyyy-mm-dd") & ", "
Next i
Range("C1").Value = Left(s, Len(s) - 2)
End Sub