Date format problems after csv import
 

Dear all Excel expert,
I am facing 2 date problems after importing from a sql database via .csv.
This field is a date + time field. Appreciate any help. Thanks in advance.

Problem 1:
Even during import, I select field as D/M/Y, but the date is imported in
Excel date format(Custom m/d/yyyy hh:mm). eg. 4/1/2010 11:58 has become 1st
April. How can I convert this to the correct date which is actually 4th Jan?
I would like the result to be in Excel date format to be mm/dd/yyyy hh:mm

Problem 2:
For any dates that are 12, the value is exported as text
'29/01/2010 9:28
How can I convert this to the same format as Problem 1?, ie. Excel date
format to be mm/dd/yyyy hh:mm

regards,

Date format problems after csv import
 

Hi,

To solve problem 2, try the following

1. Select the dates column (including the header row);
2. Go to Data Filter Custom Equals *. This will show all non date
entries I.e. all dates with month 12 or dates greater than the last date
of the month
3. Now select the output of 2 above and go to Data Text to columns
Delimited Ensure all boxes are unchecked Date DMY
4. When you click on OK, all non dates should get converted to dates

To solve problem 1, try the following:

1. Go to Format Cells (Ctrl+1) Number Category Custom Type MDY

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"YY san." wrote in message
...
Dear all Excel expert,
I am facing 2 date problems after importing from a sql database via .csv.
This field is a date + time field. Appreciate any help. Thanks in advance.

Problem 1:
Even during import, I select field as D/M/Y, but the date is imported in
Excel date format(Custom m/d/yyyy hh:mm). eg. 4/1/2010 11:58 has become
1st
April. How can I convert this to the correct date which is actually 4th
Jan?
I would like the result to be in Excel date format to be mm/dd/yyyy hh:mm

Problem 2:
For any dates that are 12, the value is exported as text
'29/01/2010 9:28
How can I convert this to the same format as Problem 1?, ie. Excel date
format to be mm/dd/yyyy hh:mm

regards,