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,

 

[Simon Lloyd]

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

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

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

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

regards

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[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

.

 

[Simon Lloyd]

You need to custom format the destination cells to dd/mm/yyyy, howeve
you are it may be that it is getting imported as text so excel i
treating it as such, if you want to upload a workbook and csv for us t
try you can join our forums for free (shown in the link below) it'
completely free, if you do join you will have the opportunity to ad
attachments to your posts so you can add workbooks to better illustrat
your problems and get help directly with them. Also if you do joi
please post in this thread (link found below) so that people who hav
been following or helping with this query can continue to do so. :

Don't forget to use a valid email address when joining as
confirmation email is sent requiring a link to be clicked back to th
forums

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 onl
selection i
MDY or DMY etc.. but this value is a date + time, therefore I thin
this i
why it doesnt work

regards
Y





Cage Forums' (http://www.thecodecage.com/forumz/showthread.php?t=178001

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[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