Excel: Create a Custom Date Format using Power Query

This week’s YouTube video was created in response to a question from a client. The client had a CSV file that contained 2 sets of dates. Ultimately they wanted to calculate the number of days between the dates.

However, when the CSV file was imported into Excel, the dates were 1) Being treated as text and 2) In the wrong format.

My client needed to convert the dates from MM/DD/YYYY to YYYY/MM/DD format. In this video I share the solution that I came up with to resolve the problem.

If you’re familiar with Power Query, you may be able to replicate this using the steps below. However if you want the details, or you just want to support my YouTube channel, please watch the video.

1. Import the CSV file into Excel
2. In the Query Editor change the data type of the date columns to Locale
3. In the Locale dialog box select Date as the Data Type and English (United States) as the Locale
4. Create 2 new custom columns using the following formulas:
   

Text.From(Date.Year([Start Date])) & "/" &
Text.PadStart(Text.From(Date.Month([Start Date])), 2, "0") & "/" &
Text.PadStart(Text.From(Date.Day([Start Date])), 2, "0")

Text.From(Date.Year([End Date])) & "/" &
Text.PadStart(Text.From(Date.Month([End Date])), 2, "0") & "/" &
Text.PadStart(Text.From(Date.Day([End Date])), 2, "0")

5. Delete the original Date columns
6. Rename the new columns Start Date and End Date to match the original names in the CSV file
7. Optionally move the new columns in order to retain the CSV file structure
8. Close the Query Editor and Load the data into the worksheet
9. In the spreadsheet create the appropriate formulas to calculate the difference between the dates
   

Download a copy of the file used in this video: https://share.zight.com/X6ujdomx