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

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

--
Message posted from http://www.ExcelForum.com

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

"Andoni " wrote in message
...
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!


---
Message posted from http://www.ExcelForum.com/

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

"Michael Chang" wrote in message
...
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

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


Michael Chang wrote:
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

"Andoni " wrote in message
...

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!


---
Message posted from http://www.ExcelForum.com/






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

"Michael Chang" wrote in message
...
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

"Andoni " wrote in message
...
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!


---
Message posted from http://www.ExcelForum.com/

Thanks for the info Debra.
"Debra Dalgleish" wrote in message
...
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


Michael Chang wrote:
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

"Andoni " wrote in message
...

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!


---
Message posted from http://www.ExcelForum.com/






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html