CODEWITHSUNDEEP
exceldatecsvformatexcel-2007
Vigneshwaran Kandaswamy
Vigneshwaran Kandaswamy

Reputation: 23

Regarding converting .CSV file dates

I am working in software. From that I got out put as .CSV format file. Now I need to convert that .CSV file in to Excel 2007 and then I have to use that file. In that I have Previous Month date and Current month date. For example I am working for May month means the date is 13/05/2013 this is the standard format, but in some cells I found the format changed to 3/5/2013 (05-Mar-13) But it should be 5/3/2013.

How to change this - any formula or formatting?

Upvotes: 0

Views: 421

Answers (1)

pnuts
pnuts

Reputation: 59450

Instead of double-clicking on the .csv file try Data > Get External Data - From Text, Import and at Step 3 of 3 format Date to suit.

Edit Attempt at clarification

I am a little confused by the OP’s comment here (it is not working when coupled with the green tick) and not quite sure whether DMY or MDY is required output but given the source as in ColumnA, then importing with DMY gives ColumnB (after formatting) and with MDY gives ColumnC (after formatting). Both Columns B and C in General format appear as numbers (B5 as 41338 and C5 as 412397 – vice versa in Row6).

eSO17964789 example

If say 1/4/13 is April 1st and 1/5/13 is 5th January then I see no solution given the data presently available. The normal problem is where Excel has a default setting such that 6/7/13 converts to July 6th for a date that is supposed to be June 7th, because the same setting can’t cope with 10/21/13 (there is no ‘month 21’). Hence some dates are left as strings, some converted (incorrectly) and only some correctly (1/1, 2/2 etc are no problem).

Upvotes: 2

Related Questions