Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). | |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: I enter date and format for date, but shows as number | Excel Discussion (Misc queries) | |||
Have Excel format xxxxxx into date format | Excel Discussion (Misc queries) | |||
How can I convert a date format to an ISO week format (in EXCEL)? | Excel Discussion (Misc queries) | |||
Excel 2000 date format cannot be set to Australian date format | Excel Discussion (Misc queries) | |||
Excel Default date for date format | Excel Discussion (Misc queries) |