If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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, |
#2
|
|||
|
|||
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, |
Thread Tools | |
Display Modes | |
|
|