Converting the Format of and Combining Dates

[RyanB]

I have two dates stored in two cells of the format March 01, 2007. I need to convert these to the format yymmdd (ie. 070301) and combin them into a single cell (ie. 070301,070331). The dates are on a different worksheet, but the same workbook, as the cell that I am trying to combine them in.

After looking through the forums on here and hours spent going through examples on the net, I have come up with the following lines:

Range("B8").NumberFormat = "General"
Range("B8").FormulaR1C1 = "=CONCATENATE(TEXT(Input!E2,""yymmdd""),"","",TEXT(Input!E3,""yymmdd""))"

They, or course, are not working. For some reason the macro is placing a single quatation (') around the cell reference (ie. Input!'E2') and so excel just has "#NAME" in the cell unless I manually take out the quotes. Any help or suggestions would be very much appreciated.

 

[barry houdini]

Surely you need to give the cell reference in R1C1 notation, i.e.

Range("B8").FormulaR1C1 = _
        "=CONCATENATE(TEXT(input!R[-6]C[3],""yymmdd""),"","",TEXT(input!R[-5]C[3],""yymmdd""))"

 

[RyanB]

Yep, that was it. Thanks for the help!

 

[Datsmart]

Another option would be to stay with your "A1" notation by dropping the R1C1:

Range("B8").Formula = "=CONCATENATE(TEXT(Input!E2,""yymmdd""),"","",TEXT(Input!E3,""yymmdd""))"