How to change date/time format from csv files

**hammer2** · 01-12-2015, 10:51 PM

Hi all
I download data that comes in csv format with date/time like this:

1/11/2015 16:00

I need to change this to 11/01/2015 16:00 when pasted into an .xlsx file but the format cells function does not change it for me.

Cheers
hammer

**Punnam** · 01-12-2015, 11:31 PM

Try this
=text(trim(1/11/2015 16:00),"mm/dd/yyyy h:mm")

Formula:

Please Login or Register  to view this content.

Punnam

**TMS** · 01-12-2015, 11:31 PM

Use Text to Columns to split the date/time using a space as the delimiter and output to two "spare" columns. In the dialogue, select the first column and format it as DMY. Then, in a third column, add the date and time columns and format as you wish.

Then, copy the third column and Paste Special | values and number formats over the original.

Now you can delete the three helper columns.

Probably sounds more complicated than it is

Regards, TMS

**hammer2** · 01-12-2015, 11:49 PM

Thanks to you both

**TMS** · 01-12-2015, 11:56 PM

You're welcome. Thanks for the rep.

**Punnam** · 01-13-2015, 12:07 AM

Hi hammer2
Thank for the reputation points
Punnam

How to change date/time format from csv files

LinkBack

Thread Tools

Rate This Thread

Display

How to change date/time format from csv files

Re: How to change date/time format from csv files

Re: How to change date/time format from csv files

Re: How to change date/time format from csv files

Re: How to change date/time format from csv files

Re: How to change date/time format from csv files

Thread Information

Users Browsing this Thread

Similar Threads

Default date/time format when importing CSV files

Need vba code to change date&time stamp as date format dd-mmm-yy in A:A rng

Rename Files in Folder - Change Date Format

Change Date format for CSV files

[SOLVED] how do I format cells to change date and time to just date

Bookmarks

Bookmarks

Posting Permissions