Excel Bug (date format)

  • Thread starter Thread starter othellomy
  • Start date Start date
O

othellomy

I have a excel file imported from some other application. When I try
to apply a formatting to a particular column that has 'date' values,
the formatting does not take effect. For example if the value in the
column is:
1/10/02
If I try to apply a date formatting (Europe) I should get 10/1/02. But
Excel does not do anything. However, if I click on the value of the
column (next to 'fx') then excel recognizes it as Date value and then
I can format it. There are thousands of rows in the file and I can not
manually click on each value so that Excel recognizes it as date
value. How can I have Excel to format it as date when it does not do
anything? The option Format-Cells and then 'Date' is not working and
it is not doing any formatting at all!

What I had done is that originally the column had values like 'SU MTH/
1/02 (Thousands)' and after doing a formula: concatenate("01",mid...) I
get 01/1/02 (I added first of each month) which is European date (day/
month/year) and then I want to apply the formatting for English (UK)
so that excel recognizes it as European date and then formats is as
valid date as English(US).
 
Hi

Try selecting the column of dates and going Data>TextToColumns and
click Next>Next and then select the import type to be Date and choose
the sequence (DMY by the sounds of it) and click Finish. This should
convert them all to recognised dates.

Incidentally, when you create the date (by stripping the text), if you
add 0 on the end eg:

=("01" & MID(A1,8,5))+0

the conversion should be done automatically for you.

Hope this helps!

Richard
 
Your dates are text.
Format an empty cell as number. Edit>Copy. Select your "dates". Edit>Paste Special, check Multiply.
Format as date.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a excel file imported from some other application. When I try
| to apply a formatting to a particular column that has 'date' values,
| the formatting does not take effect. For example if the value in the
| column is:
| 1/10/02
| If I try to apply a date formatting (Europe) I should get 10/1/02. But
| Excel does not do anything. However, if I click on the value of the
| column (next to 'fx') then excel recognizes it as Date value and then
| I can format it. There are thousands of rows in the file and I can not
| manually click on each value so that Excel recognizes it as date
| value. How can I have Excel to format it as date when it does not do
| anything? The option Format-Cells and then 'Date' is not working and
| it is not doing any formatting at all!
|
| What I had done is that originally the column had values like 'SU MTH/
| 1/02 (Thousands)' and after doing a formula: concatenate("01",mid...) I
| get 01/1/02 (I added first of each month) which is European date (day/
| month/year) and then I want to apply the formatting for English (UK)
| so that excel recognizes it as European date and then formats is as
| valid date as English(US).
|
 
Hi

Try selecting the column of dates and going Data>TextToColumns and
click Next>Next and then select the import type to be Date and choose
the sequence (DMY by the sounds of it) and click Finish. This should
convert them all to recognised dates.

Incidentally, when you create the date (by stripping the text), if you
add 0 on the end eg:

=("01" & MID(A1,8,5))+0

the conversion should be done automatically for you.

Hope this helps!

Richard




- Show quoted text -
Click to expand...
=("01" & MID(A1,8,5))+0
Click to expand...

Thanks a lot. That did the trick!
 
If the text "dates" are in a single column, I'd do this:

Select the range (single column at a time)
Data|Text to columns
Fixed width
remove any lines that excel guessed
and tell it you want it treated like a date mdy or dmy or whatever you want.

And finish up by plopping the converted values in the same spot.

Repeat for each column in your data.
 
ps. I'd check that I converted the dates correctly by giving them a
non-ambiguous format (even temporarily)--like:

mmmm dd, yyyy
 
ps. I'd check that I converted the dates correctly by giving them a
non-ambiguous format (even temporarily)--like:

mmmm dd, yyyy








--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks!
 
Back
Top