How can I get my formula to 'paste special values' to preserve date formats?

I have a worksheet that, when filled out, can convert the completed fields into a set of summarizing notes. To do this I am using the CONCATENATE formula to piece together several different fields into one string of text. Then a macro button that will copy the cell containing that formula and paste special values into another cell. Out come being one cell with copy-ready text that can be easily grabbed and pasted into another program.

I'm having trouble with one field. The field is for a date of birth and the target cell is formatted as 3/14/81 (for March 13th 1981). However, when my formula grabs that cell it translates it to 29659 for some reason. I get a similar problem with phone numbers because the target cell is formatted to display properly, but what is entered and used by the formula is actually just 5555555555.

Is there a way to get my formula to use the paste special values functionality so it grabs how a cell is displaying rather than what is in it, if that makes sense?

If it helps, here's an example of my formula. The date is located in cell I17.

---

Formula:

=CONCATENATE("FAXED AUTH PACKET TO PAYER", CHAR(10), CHAR(10), E17, " ", F17, CHAR(10), H17, " ", I17, CHAR(10), "PA ", E19, " ", F19, CHAR(10), "PA ", H19, " ", I19)

---

I know I could accomplish this by running a macro to convert and then grabbing from the new location but to avoid additional button clicks by users I would have to do it in a VB script and I would like to avoid it if possible and keep this simple.

Thanks in advance.

Thanks for your reply. For some reason that's not working. I've even tried taking the formatting off the target cell and tried formatting it as text and general. Even then, entering 3/14/81 into that cell, although it displays as such, shows 3/14/1981 in the formula bar. So it changes my input and no matter what I do to my formula, it reads that as 29659 in my destination cell. I also noticed that when I enter a date in the target cell it changes the formatting to date (which is a bit annoying)

Any thoughts?

Thanks again.

Thank you so much for your help! I shouldn't admit this but I was placing your string on the wrong cell. Sorry. Thanks so much for taking the time to help me, this makes a HUGE difference!

Much appreciated.