Copy n Paste time date/time format

**jazbath** · 10-12-2009, 05:33 PM

Hi,
I have a cell with both date & time "10/9/09 3:15" This is put in the current cell by formula which indexes two dif. cells, Now I am trying to copy this cell and paste into another book but like to have only date. How can I do that?
Each time I try it gives me the time value in the pasted cell and I cannot even format it. Please help.
Thank
Jaz

**JBeaucaire** · 10-12-2009, 05:35 PM

Yes, you can reformat any cell with this kind of value in it.

1) Select the cell/range
2) Press Ctrl-1 to open the format cell window
3) Select Number format - Date...your choice

You don't have to change the cell value to format it to display to your liking.

I suppose there is a chance your formula that creates these values is making a text string. Care to show us that formula?

**teylyn** · 10-12-2009, 05:36 PM

Hi,
what exactly do you paste? The formula or the value? What does the formula bar show after you've pasted? What is the format of the cell before pasting? After pasting?

**JBeaucaire** · 10-12-2009, 05:39 PM

If the pasted date IS text strings and not really Excel date values, try this:

1) Select the cell/range
2) Click on Data > Text to Columns > Delimited > Next > Next
3) In the upper right corner select Date type: Date: MDY
4) Click Finish

That might fix it for you.

**jazbath** · 10-12-2009, 06:57 PM

JBeaucaire,
The Text to Col. method helped me. Thank you.

**jazbath** · 10-12-2009, 07:01 PM

Teylyn,
I did not noticed what the format was before I pasted, but it was a fresh cell where I was pasting. the cell I copied from carried both date and time, so I copied anyway and wanted to paste only date not time. text to Col. method let me eliminate the unwanted time stamp.
Thank you anyway
Jaz