[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Date format in CSV file

Posted on 2014-07-14
14
Medium Priority
?
309 Views
Last Modified: 2014-07-15
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.
0
Comment
Question by:mrong
14 Comments
 
LVL 28

Expert Comment

by:MacroShadow
ID: 40194253
Open the file
Select the column
Right click
Cell format
Set the format to dd-mm-yyyy
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40194260
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
0
 

Author Comment

by:mrong
ID: 40194279
MacroShadow,

I tried this at very beginning but it put data from 'dd/mm/yyyy' to 'mm-dd-yyyy'
Thanks.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:mrong
ID: 40194287
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.
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40194288
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
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40194292
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...
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40194293
type dd-mm-yyyy in the input box at Category after clicking custom

Be reminded, work around , you will get it  

Duncan
0
 

Author Comment

by:mrong
ID: 40194396
duncanb7,

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

I use office 2013.
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40194413
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
0
 

Author Comment

by:mrong
ID: 40194431
Duncan,

Yes, I did exactly same as you suggest but it shows format of 'mm-dd-yyy' format.
0
 
LVL 13

Accepted Solution

by:
duncanb7 earned 2000 total points
ID: 40194460
how about you type mm-dd-yyyy into the box

What is diffferent result when type dd-mm-yyy and mm-dd-yyyy

Two tests, try it , work around, you will get it

Duncan
0
 

Author Comment

by:mrong
ID: 40195176
it still show dd-mm-yyyy for some records.
0
 

Author Comment

by:mrong
ID: 40195178
Sorry, too early to say.
That fixes the issue.
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40196661
Thanks for your points

Have a nice day

Duncan
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question