How to Convert Website Dates to YYYY-MM-DD in Power Query
Question details
The user needs to convert imported website dates (e.g., MM/DD/YY) into a valid date data type and display them in the YYYY-MM-DD format.
- Product
- Power Query / Excel
- Device & OS
- not provided
- Scenario
- Importing data from a website where dates are stored as text in regional formats and need to be standardized into a uniform YYYY-MM-DD layout.
- Observed behavior
- The imported dates are recognized as text strings or return errors when converted, preventing proper sorting, filtering, and uniform YYYY-MM-DD formatting.
Ensure your dataset is loaded into the Power Query Editor and identify the specific column containing the imported website dates.
Convert Text to Date Using Locale Settings
This is the most reliable method for converting regional text dates (like MM/DD/YY) into valid date values without writing custom code.
Website dates often import as text based on the source's regional format (e.g., US format MM/DD/YY). By using the 'Using Locale' feature, you tell Power Query exactly how to read the incoming text so it can safely convert it to a universal Date data type.
Right-click the column header containing your text dates in the Power Query Editor, hover over 'Change Type', and select 'Using Locale...'.
In the pop-up window, set the 'Data Type' drop-down to 'Date'. Set the 'Locale' drop-down to the region matching your source data (e.g., 'English (United States)' for MM/DD/YY).
Click 'OK'. The column will now be converted to a proper Date data type without throwing errors.
Click 'Close & Load' to return the data to your spreadsheet. Select the date column, right-click and choose 'Format Cells', go to 'Custom', and type 'yyyy-mm-dd' to set the visual display format.
Use Date.FromText for Custom Date Parsing
Use this method if you prefer to use Power Query M-code to generate a new column with the parsed dates.
Convert Date to Text Output (YYYY-MM-DD)
Use this approach only if your final output strictly requires a text string instead of a functional date value.
Format Dates Easily in WPS Spreadsheet
You don't always need complex Power Query formulas to fix imported website dates. WPS Spreadsheet offers intuitive, built-in tools like Text to Columns and Custom Formatting to quickly convert and format your dates to YYYY-MM-DD without writing code.
- 1. Select Your Data: Highlight the column in your spreadsheet containing the imported website dates.
- 2. Use Text to Columns: Go to the 'Data' tab on the ribbon and click on 'Text to Columns'.
- 3. Convert Text to Date: Proceed to Step 3 of the wizard. Choose 'Date', select the 'MDY' format from the drop-down, and click 'Finish'.
- 4. Open Format Cells: Right-click the newly converted column and select 'Format Cells'.
- 5. Apply Custom Format: Go to the 'Custom' category, enter 'yyyy-mm-dd' in the Type box, and click 'OK' to apply the formatting.
Frequently Asked Questions
Why does Power Query return an error when converting my dates?
This usually happens because the date format of the source text (e.g., MM/DD/YY) does not match your computer system's default regional settings. Using 'Change Type > Using Locale' allows you to specify the exact origin format (like English - United States), which prevents conversion errors.
Can I format the date as YYYY-MM-DD directly inside Power Query without converting to text?
Power Query's 'Date' data type does not store display formatting; it automatically uses your system's default short date format visually. To display YYYY-MM-DD as a functional date, you should load the converted Date data type into your spreadsheet and apply custom cell formatting there.
What is the difference between Date.FromText and Date.ToText in M-code?
Date.FromText parses a text string and turns it into a standard Date data type, which is necessary for chronological sorting and calculations. Date.ToText does the exact opposite—it takes a valid Date data type and converts it back into a text string with a specified display layout (like 'yyyy-MM-dd').
