Converting the Format of and Combining Dates

RyanB

New Member
Joined
Jul 18, 2007
Messages
2
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Surely you need to give the cell reference in R1C1 notation, i.e.

Code:
Range("B8").FormulaR1C1 = _
        "=CONCATENATE(TEXT(input!R[-6]C[3],""yymmdd""),"","",TEXT(input!R[-5]C[3],""yymmdd""))"
 
Upvote 0
Another option would be to stay with your "A1" notation by dropping the R1C1:
Code:
Range("B8").Formula = "=CONCATENATE(TEXT(Input!E2,""yymmdd""),"","",TEXT(Input!E3,""yymmdd""))"
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top