How to control date format when importing CSV files to Excel 2016

I'm trying to import a CSV file whose first column contains dates in mm/dd/yy format, e.g. "4/28/18".    You'd think it would be simple...

When I use the new Excel 2016  Data -> Get Data -> From Text/CSV function and use the Edit option, I can select Date, but I have no control over the format.  It tries to interpret them as YYYY-MM-DD, and of course that produces junk.

It may be that this problem wasn't an issue with previous versions of Excel (where you could define custom date formats), but the old Text Import Wizard had been replaced and no longer appears to be available.

Surely there must be _some_ way to tell Excel what date format to use when importing!  

-- Ken

Hello Ken,

Below are some solutions to save excel files in general but I think that can help you https://support.microsoft.com/es-us/help/271513...

Regards.
Liliana De Freitas

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Thank you Liliana, but I don't see how that relates to my question. 

I'm having problems with IMporting an existing CSV file, not with saving or exporting one.  Excel is not reading the dates properly.

-- Ken

--- If you're not making mistakes, try harder. ---

5 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Do what you are doing but when you get to Step 3 of 3 in the Text Import Wizard, select the date column in the bottom of the screen and then select "Date" option and then click the DropDown to the right of the Option button and select the date format that has been used in the text file.

Don't select the date format that you want to finish up. Select the existing date format in the text file. Excel will import the date and then convert it (if necessary) to the regional date format for your computer.

Basically you need to tell Excel what the format of the date is in the existing text file so it knows how to convert it.

Regards,

OssieMac

3 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hello
Review this article to help you with you request.

https://support.echo360.com/customer/portal/art...


Disclaimer: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.
Volunteer, No Microsoft Agent.
Feel free to pick a rate if your question is resolved.

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hello again Ken, I am sorry to hear that.
The file was exported with an specific time zone from the computer or database where was exported, check the time zone set in the computer used to import the file.
Liliana De Freitas

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hello again Ken, I am sorry to hear that.
The file was exported with an specific time zone from the computer or database where was exported, check the time zone set in the computer used to import the file.

Liliana, if I had control over how the CSV was created, I wouldn't be asking how to import it.  The CSV was created by a website over which I have no control, no access, and no idea what software was used.

-- Ken

--- If you're not making mistakes, try harder. ---

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Thanks OssieMac, but those instructions won't work. 

The latest Excel 2016 does not have a Text Import WIzard, don't ask me why.  It uses Data -> Get Data -> From Text/CSV function.  I see no way to "select the date column in the bottom of the screen" as you describe.  It only has the Get Data function that is on the Data tab/screen.

Even Microsoft Support doesn't appear to know how to use it, and of course Microsoft documentation is crap these days.

--Ken

--- If you're not making mistakes, try harder. ---

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hello
Review this article to help you with you request.

https://support.echo360.com/customer/portal/art...


Disclaimer: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.

Thank you Ed_22, but those instructions do not address my issue. 

They are about formatting the display of dates that have already been imported, and say "Some of the CSV imports contain date fields, in particular the capture/schedules import. The required format for date fields is yyyy-mm-dd. This is an international standard date format, designed to reduce confusion.

While I certainly wish everyone, particularly Americans, would get their heads out of their butts and use that lovely unambiguous ANSI standard date format... they don't.  And this article doesn't say how to import dates from CSV files that are created in other formats.  It only says how to reformat the display once the import is done.  Which is too late.

So, another lose.

-- Ken

--- If you're not making mistakes, try harder. ---

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Triggered by something OssieMac said, I think I have this figured out.  Microsoft please take note: you should better document this.  Here's how it works:

When using the Get Data -> From Text/CSV function and using the Edit option, DON'T use the Data Type selection over the Transform group/tab. 

Instead, right-click the column header over the column containing the dates, and select Change Type -> Using Locale.  Then, in the dialog that appears, select data type Date and a locale that uses the date format that matches the CSV date format, and click OK. 

In this case I needed mm/dd/yy.  There are a LOT of options and you might have to search, but at least it does show examples for the one you pick.  In my case English(United Stated) worked perfectly.

-- Ken

--- If you're not making mistakes, try harder. ---

59 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I'm sorry to hear that it was no helpful will see if another member could assist in you request.
Volunteer, No Microsoft Agent.
Feel free to pick a rate if your question is resolved.

2 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated April 12, 2021 Views 21,782 Applies to: