Concatenate Date, copy/paste value

**jerrydiaz** · 07-22-2013, 03:27 PM

Hi-

I'm not sure why this isn't working correctly. I have a spreadsheet that needs to concatenate dates (because of a multi-function search & replace macro, I need to keep fields separated). So cell
A7 is 4/13/2013
A8 is =TEXT($A$7,"mmmm")
A9 is 13, 2013 and
A10 is =CONCATENATE(A8,A9)

I need a macro to copy the concatenated value in A10 and paste its value in A11. It does just that, but it doesn't seem it's legitimate date unless I click into the function bar and hit enter (Which doesn't seem to record in macro properly). It either remains as text (despite the cell formatting), or converts it to a duplicate of A10's formula.

The ultimate goal is one macro which will do a search (with prompt dialog boxes) for "April" (for example), replace it with "May" or "March" or whatever I need that particular session and update cell A11 as an actual date with the new month. I also have formulas which refer to external workbooks that contain worksheets named after each respective month, (i.e., text - not an actual date). So I need the search to serve both functions.

Thanks so much

**Jonmo1** · 07-22-2013, 03:33 PM

Why not just put in A11

=A7

And format A11 as your desired date format mmmm dd, yyyy

Or even better, just apply that format to A7?

**jerrydiaz** · 07-22-2013, 06:32 PM

Because of a multi-function search and replace macro, I cannot search the full date in the macro because it is also performing other functions.

Excel actually stores the date as a string of numbers (i.e., 4/13/13 is actually 41377 until reformatted as a date. Therefore you cannot search just "April."
I need to be able to keep the fields separate.

What I need does work correctly as long as I click into the formula bar and hit enter. But I don't know how to make that happen in a macro, because it just records the action as though I had manually entered the text.
Anybody please?