Date mm/yy

  • Thread starter Thread starter naomi
  • Start date Start date
N

naomi

Hi am trying to copy data into Excel. I am having trouble with the date
column, which should be in the format mm/yy. However, when I copy it in it
changes. For example 04/08 (meaning April 2008) will automatically change to
4-Aug. Itried changing the cell format to 'text', and I can enter data in ok
this way, but when I copy it in it goes back to the old 4-Aug. I am talking
about large quantities of data, so can not enter them all in manually. Can
someone help??
 
HI,

This is happening because excel is by default set to recognize the date
format as US English. You can go to format select date from the number tab
and in the drop down list (local location) select English (United Kingdom)
and excel with recognize your date format.

Hope this helps you.
 
Think part of the problem you face lies also in the ambiguity in the "date"
data that you're pasting. It doesn't have the day part.

Try it in this sequence
1. Pre-format the entire col A (say) as TEXT
2. Do a "Paste special" as values of the data that you've copied into col A
3. Put in B1: ="01/"&A1
(this is to attach a "day" part to the pasted strings, eg: "04/08")
Copy B1 down, then freeze col B with an "in-place" copy n paste special as
values
4. Select col B, click Data > Text to Columns. Click Next>Next. In step3 of
the wiz., check "Date", select: DMY from the droplist. Click Finish. Then
just apply a custom format of: mmmm yyyy to get the desired results in col B.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
 
Back
Top