Solve date format problems with CSV import in Excel

#msexcel #datacleaning #efficiency365
Importing dates from CSVs can be tricky. If the input date format and the PC date format does not match, days and months get swapped. Half the dates will remain text.
Here you can see two solutions for this problem: one using Excel native feature - Text import wizard and second - a modern solution - using Power Query.

Contents
---------------

00:00 Introduction
00:26 Problem
02:47 Root cause
03:12 Solution1
05:13 Best solution - Power Query

The first solution will work with any version of Excel. Second solution requires Power Query which is available with newer versions, including Office 365 version.

Buy Dr Nitin's latest book
-----------------------------------------
Efficiency Best Practices for Microsoft 365
130 best practices everyone must know
Work Smarter, Grow Faster

Dr Nitin Paranjape is the pioneer in maximizing work efficiency in a modern work scenario.
Dr Nitin's Office Masterclass
----------------------------------------------

Udemy Courses by Dr Nitin Paranjape
--------------------------------------------------------------

Learn more from 1000+ blog articles by Dr Nitin Paranjape

Production
--------------------

[sarabjeetsinghkhanuja]

Wasted 2 hours working out the solution until I found this Video, thanks a ton!! Solved in less than a min

[Pandey-pooja]

Thank you so much, Nitin . Spent 4-5 hours just to figure out this via different videos . Finally, found your guide and within few min it was all sorted .

[jyothsnareddy]

Thank you Dr. Nitin! Your way of explanation is wonderful. This solution saves many

[mariaemiliac]

Thank you Dr. Nitin! This explanation was very enlightening and clear! It served me a lot!

[ersofrescht]

I have had this problem for over two days and this just solved it! I have a column of dates in a csv file and it's formatting differently for the 01/1-12 days and 1/13-up days. When I load it to power BI it just wont recognize the 01/13-up dates and returns an error. I tried formatting it from the CSV but somehow it is not taking custom format. I even did text to columns as well as extract function in power just to get it right but even then I am stuck with a dd/mm/yy. I still couldn't make it to a mm/dd/yy format. I just realized I had to change the regional setting in the control panel to resolve this. Anyhow, your method is simple but it solves this date conundrum that I've had easily. Thank you so much! I am so relieved to say the least! More power to your channel! Subscribed!

[juliesolace]

Thank you so much sir. I can't believe I spent more than 3 hours just trying to fix this date problem, glad I finally conquered it. Pheew.

[jubran]

Other videos just give alternatives to set a format but without defining the issue. this video Master them all. much appreciated for the demonstration. I have changed my pc date format then followed the PowerQuery method. it works perfectly.

[TheOnlyGazzLam]

That's a great guide, thank you.
Unfortunately I only need it for about 15 mins of work and I've now got a powershell script to flip my locale settings between DD/MM/YYYY and YYYY-MM-DD. Which is much faster for what I need.
I did learn something about Excel though, which is great

[muhammadassadansari]

Thanks for the video Sir. Gold for you!

[r]

Thank you so much! This has been bothering me for so long, now I finally know the solution!

[hariharagupta]

Sir, thanks for the video, i was pulling the report from one of the application, upon this video i have changed the regional settings in the application then downloaded the report, it calculates as date format... thank you very much, this really helped me

[varshasaji]

Sir, I have a csv file which I want to update (add more data). Now, if I follow this method, update the data and then convert it back to a csv using the save as--> csv option, will it work? or do I need to change the formatting again?

[marcvillalobos]

Dr. Nitin,
I have an issue where I am exporting very old files from the year 2000 from a paid data repository in excel csv format. The date format in the repository shows as (MM-DD-YY). When I open the csv file, the date format is not entirely intact. Some of the dates are preserved in MM-DD-YY, but most are changed to YYYY-MM-DD. But does this in the worst way, by adding (20) before the month and turning it to a YYYY. For example, a record dated 12-10-01 (Dec, 10, 2001) becomes 2012-10-01 (2012, Oct, 01). The entire year, month and day is wrong. It is really wierd. Then, when I convert the .csi file to Txt, as in your tutorial, the date will appear exactly as it does in the csv file, e.g., in both formats. Its really messed-up. Have you seen anything like this?

[AbhisheksChannel]

What if it's not a csv but an excel file itself that has dates in different format than my locale. Then when I open it on my Indian locale computer, will it read and store dates correctly? I want it to retain the original format mm/dd/yy and not change it to dd/mm/yy.

[balajirajagopal]

Appreciate the directions. Was one issue that was causing me nightmares in my data analysis. Is there a way to set the Locale permanently.

[shilpaanthati]

Sir,
I am facing issue when dowloading excel csv.CSV changes to wrong dates upon save. for ex : when I enter 12-Oct-1945, saving in 12-oct-45 format. when I tried to read that cell value it is showing as 12-oct-2045.could you please help with solution

[_LearnWithChris]

Hi Dr Nitin,
Appreciate your content. Would be great help if you could include a way to convert datetime function as 'yyyy/mm/dd hh:mm:ss' . My only solution thus far is to save as a .CSV file as files will reformat datetime into locale format.
Thank you

[kitakamidesign]

I already tried both methods but didnt work. How to resolve this problem?

[vijay]

Sir just wanted to know in CSV there is new line automatically coming when i clone the csv from git how to avoid automatic new line for eg <Status>Failed</Status>
<TechnicalStatus>Data Validation</TechnicalStatus>
<Details>FileName cannot be Empty</Details> this data will turn to <Status>Failed</Status>

<TechnicalStatus>Data Validation</TechnicalStatus>

<Details>FileName cannot be Empty</Details>