Convert CSV Date and Time Values to yyyy-mm-dd hh:mm in Excel
Question details
Convert CSV date-time entries formatted as mm/dd/yyyy hh:mm:ss AM/PM into real Excel date-time values and show them as yyyy-mm-dd hh:mm.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- The user is importing a CSV file containing U.S.-formatted date and time values.
- Observed behavior
- Opening the CSV directly can cause Excel to interpret dates using the computer's regional settings instead of the CSV's U.S. locale.
Confirm that the source column consistently uses the mm/dd/yyyy hh:mm:ss AM/PM pattern. Use Power Query to import the file rather than double-clicking the CSV, which applies automatic regional date parsing.
Import the CSV with Power Query and apply the English (United States) locale
Use this method when Excel may misread the month and day order while opening a CSV file directly.
Power Query lets you keep the column as text during import and then explicitly convert it with the en-US locale. This prevents ambiguous dates such as 04/05/2024 from being interpreted with the wrong date order.
In Excel, select Data > Get Data > From File > From Text/CSV, choose the CSV file, and select Transform Data instead of Load.
In the Power Query Editor, select the date-time column. If it was detected as another type, select Transform > Data Type > Text.
With the column selected, choose Transform > Data Type > Using Locale. Set Data Type to Date/Time and set Locale to English (United States), then select OK.
Select Home > Close & Load to return the converted date-time column to the Excel worksheet.
Select the imported cells in Excel, press Ctrl+1, choose Custom, enter yyyy-mm-dd hh:mm in the Type box, and select OK.
Set the locale in the Power Query formula for a repeatable import
Use this option when you maintain or edit an existing Power Query query and want the locale conversion defined in the query step.
Work with CSV Data in WPS Spreadsheet
If you need a free and lightweight spreadsheet application for opening, reviewing, and editing CSV data, WPS Office provides a familiar spreadsheet workspace with a Microsoft Office-like interface. You can move existing workbook data to WPS Spreadsheet seamlessly and keep common Excel file formats compatible.
- 1. Open the CSV file: Launch WPS Spreadsheet and select Menu > Open to choose the CSV file.
- 2. Review the imported date column: Select the date-time column and verify that entries are displayed consistently before editing or exporting.
- 3. Save in an Excel-compatible format: Select Menu > Save As and choose Excel Workbook (*.xlsx) when you need to share the file with Excel users.
Frequently Asked Questions
Why does Excel change the day and month when I open a CSV file?
Excel often uses your Windows or Microsoft 365 regional settings when opening a CSV directly. Import through Data > Get Data > From Text/CSV, then use Transform > Data Type > Using Locale and select English (United States).
What locale should I choose for dates written as mm/dd/yyyy with AM or PM?
Choose English (United States) and set the data type to Date/Time. This locale recognizes the month/day/year order and the 12-hour AM/PM time notation.
How do I remove seconds from imported CSV date-time values without changing the actual date?
After loading the data, select the cells, press Ctrl+1, choose Custom, and enter yyyy-mm-dd hh:mm. This hides seconds in the display while retaining the underlying date-time value.
Can I refresh the CSV later without repeating the locale conversion?
Yes. When the locale conversion is saved as a Power Query step, use Data > Refresh All to reload the CSV and apply the same en-US Date/Time conversion automatically.
