date format problem

[glen.e.mettler]

using 2003. I receive a file that contains date information in cells
formated as General. The dates are in the format mm/yy (ie 03/09). I
changed the cell type to "date" and changed the date format from mm/yy
to month/year (ie Mar-09) format. However, it does not refresh even
if I recalc. However, when I double click the cell and then move to
the next cell it refreshes that cell and I get the proper date
format. Anybody know what's going on and how do I get the whole sheet
to refresh? I have thousands of rows.

meg99

 

[Niek Otten]

Copy an empty cell.

Select your dates. Paste Special, check Add, format as required.

 

[Dave Peterson]

The values in those cells aren't dates. It's just text that looks like dates.

You could try:
Select the cells with these text entries
format the cells as General
edit|replace
what: /
with: /
replace all

But then
03/08 will be converted to March 8, 2009 (using my USA settings (mdy order)).

If your regional settings are in mdy order and you want to convert
03/08 to March 1, 2008, you could try this:
Insert a helper column to the right
Use a formula like:
=--SUBSTITUTE(A1,"/","/01/")
Format the cell as Date
Convert the formulas to values and delete the original column of data.

 

[Harlan Grove]

using 2003.  I receive a file that contains date information in cells
formated as General.  The dates are in the format mm/yy (ie 03/09).  I
changed the cell type to "date" and changed the date format from mm/yy
to month/year (ie Mar-09) format.  However, it does not refresh even
if I recalc.  However, when I double click the cell and then move to
the next cell it refreshes that cell and I get the proper date
format.  Anybody know what's going on and how do I get the whole sheet
to refresh?  I have thousands of rows.

Changing number formats doesn't change cell contents. If a cell has
General number format but displays something like 03/09, then the cell
contents are text rather than number. When you change the number
format, you don't change the cells' text contents, so that text
continues to display the same.

When you double click on a cell you're effectively editing it. When
you then select another cell, you're effectively entering the cell
you're leaving, and THAT changes the cell contents from text to
number, and that's when you see the date in the cell's number (date)
format.

If all your date cells contain a /, the expedient way to change all
such cells' contents in a single operation would be Edit > Replace,
finding / and replacing it with /. That may see a do-nothing
operation, but it effectively re-enters all cells, thus changing their
contents from text to number.

 

[glen.e.mettler]

Changing number formats doesn't change cell contents. If a cell has
General number format but displays something like 03/09, then the cell
contents are text rather than number. When you change the number
format, you don't change the cells' text contents, so that text
continues to display the same.

When you double click on a cell you're effectively editing it. When
you then select another cell, you're effectively entering the cell
you're leaving, and THAT changes the cell contents from text to
number, and that's when you see the date in the cell's number (date)
format.

If all your date cells contain a /, the expedient way to change all
such cells' contents in a single operation would be Edit > Replace,
finding / and replacing it with /. That may see a do-nothing
operation, but it effectively re-enters all cells, thus changing their
contents from text to number.

Amazing! Who woulda thunk it?! Thanks

meg99