Date format problems after csv import

[YY san.]

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,

 

[YY san.]

Hi Simon,
Thanks for responding.
I import the csv file into excel, at the stage of the "TEXT to COLUMN"
wizard, i did set the cell to the desired date format. The only selection is
MDY or DMY etc.. but this value is a date + time, therefore I think this is
why it doesnt work.

regards,
YY

Do you get this problem if you format the destination cells to your
required date format first?

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,

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=178001

Microsoft Office Help

.

 

[Ashish Mathur]

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