Excel Bug (date format)
 

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).

Excel Bug (date format)
 

Hi

Try selecting the column of dates and going DataTextToColumns and
click NextNext 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


On 6 Mar, 06:37, wrote:
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).

Excel Bug (date format)
 

Your dates are text.
Format an empty cell as number. EditCopy. Select your "dates". EditPaste Special, check Multiply.
Format as date.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message ups.com...
|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).
|

Excel Bug (date format)
 

On Mar 6, 3:14 pm, "RichardSchollar"
wrote:
Hi

Try selecting the column of dates and going DataTextToColumns and
click NextNext 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

On 6 Mar, 06:37, wrote:



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).- Hide quoted text -

- Show quoted text -

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

Thanks a lot. That did the trick!

Excel Bug (date format)
 

On Mar 6, 6:22 pm, wrote:
On Mar 6, 3:14 pm, "RichardSchollar"
wrote:





Hi

Try selecting the column of dates and going DataTextToColumns and
click NextNext 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

On 6 Mar, 06:37, wrote:

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).- Hide quoted text -

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

Thanks a lot. That did the trick!- Hide quoted text -

- Show quoted text -


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

Excel Bug (date format)
 

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.

wrote:

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).

--

Dave Peterson

Excel Bug (date format)
 

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

mmmm dd, yyyy



wrote:

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).

--

Dave Peterson

Excel Bug (date format)
 

On Mar 6, 7:13 pm, Dave Peterson wrote:
ps. I'd check that I converted the dates correctly by giving them a
non-ambiguous format (even temporarily)--like:

mmmm dd, yyyy





wrote:

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).

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks!