Cell formatting not updating

[Michael Chang]

Hi,

I have a problem with formatting in cells not updating until I double click
into and then get out of the cell.

The cells contain data exported from Access as 7/2003, 8/2003, 9/2003 etc.
Once in Excel I am setting the cell number format as "mmm-yy". Excel seems
to recognises the data as dates but doesn't update the formatting until I
double click on the cell and click out of it.

I made sure that Calculation is set to Automatic. Pressing F9 doesn't update
the cell either. Can anyone shed some light on this?

Thanks,
Michael

 

[Andoni]

sorry if i am Wrong!
But I think, i use to from VBA:
go to data/text to columns and press finish
and in my work use to work, but I haven't use it long time ago

 

[Michael Chang]

Thanks. That worked.

It's a helluva lot better then double clicking into 4000 cells but I guess I
was hoping to find out why this happens and if there some setting in Excel
which can be changed to prevent it.

Regards,
Michael

 

[John Howard]

Hi Michael,

Excel is obviously not accepting the data as date format in the initial
import whick by the way is quite normal but a nuisance.
A workaround is to us the Datevalue funtion in a separate column, format the
new column as "m/yy" then Copy/PasteSpecial/Value the new column over the
original column.

HTH

 

[Debra Dalgleish]

For Excel 2002, the problem with Access data has been fixed in Office XP
Service Pack 3. There is information in the following MSKB article:

Numbers that are copied from Access 2002 paste as text in Excel 2002
http://support.microsoft.com/default.aspx?id=328933

Until you install the service pack, there are techniques here for
converting the data to numbers:

http://www.contextures.com/xlDataEntry03.html

Thanks. That worked.

It's a helluva lot better then double clicking into 4000 cells but I guess I
was hoping to find out why this happens and if there some setting in Excel
which can be changed to prevent it.

Regards,
Michael

 

[John Howard]

Hi Michael,
Im not sure why this happens and as far as Ive found out so far, there is no
standard Excel setting to fix it.
If you are encountering the problem on regular basis it may be worhtwhile
wristting a Macro to incorporate the Datevalue fix

Regards

Thanks. That worked.

It's a helluva lot better then double clicking into 4000 cells but I guess I
was hoping to find out why this happens and if there some setting in Excel
which can be changed to prevent it.

Regards,
Michael

 

[John Howard]

Thanks for the info Debra.

For Excel 2002, the problem with Access data has been fixed in Office XP
Service Pack 3. There is information in the following MSKB article:

Numbers that are copied from Access 2002 paste as text in Excel 2002
http://support.microsoft.com/default.aspx?id=328933

Until you install the service pack, there are techniques here for
converting the data to numbers:

http://www.contextures.com/xlDataEntry03.html