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
|