Link to home
Start Free TrialLog in
Avatar of mrong
mrong

asked on

Date format in CSV file

Greetings,

I have the format in a same column of 'dd-mm-yyyy' and 'dd/mm/yyyy' in a csv file.
How to format it all to dd-mm-yyyy format?

Thanks.
Avatar of Joe Howard
Joe Howard
Flag of United States of America image

Open the file
Select the column
Right click
Cell format
Set the format to dd-mm-yyyy
Avatar of duncanb7
duncanb7

On Excel Sheet, select the cell or range cell you want, press ctrl+1, select date, choose
the format you want from scrolling the side bar
check at the Microsoft  atricle as follows
http://office.microsoft.com/en-001/excel-help/format-a-date-the-way-you-want-HA102809474.aspx


Duncan
Avatar of mrong

ASKER

MacroShadow,

I tried this at very beginning but it put data from 'dd/mm/yyyy' to 'mm-dd-yyyy'
Thanks.
Avatar of mrong

ASKER

duncanb7,

Followed the link you provided and it also put data from 'dd/mm/yyyy' to 'mm-dd-yyyy'.

I defined the custom format as 'dd-mm-yyyy'.

thanks.
There is custom item at the bottom of the left-hand-side-menu , click it, and
choose dd-mm-yyyy or type dd-mm-yyyy in the input box at Category

Duncan
working with excel to "fix" csv data is not the best tool for the job.
first of all: where does the data come from... usually the issue should be fixed "at the very first source"

for a one-time job, I would write a small whatever(*) script to change the relevant / into -

(*) could be a vba macro, vbscript, a vb6, C#, php ...
in short: anything that you are comfortable with, to do a quick "read line by line", and do a replace.
in unix worlds, this could be a quick awk script...
type dd-mm-yyyy in the input box at Category after clicking custom

Be reminded, work around , you will get it  

Duncan
Avatar of mrong

ASKER

duncanb7,

I don't see custom item at the bottom of the left-hand-side-menu.

I use office 2013.
check at the image once you press ctrl+1 kry ( ctrl with 1)
http://officeimg.vo.msecnd.net/en-001/files/678/453/ZA102841669.jpg

At Categroy, there is item such as number, date.....custom
and At the "Type", type dd-mm-yyyy

Be remined select the cell or range you want first

Duncan
Avatar of mrong

ASKER

Duncan,

Yes, I did exactly same as you suggest but it shows format of 'mm-dd-yyy' format.
ASKER CERTIFIED SOLUTION
Avatar of duncanb7
duncanb7

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mrong

ASKER

it still show dd-mm-yyyy for some records.
Avatar of mrong

ASKER

Sorry, too early to say.
That fixes the issue.
Thanks for your points

Have a nice day

Duncan