How to Convert Imported US Dates to UK Date and Time Format in WPS Spreadsheets
Question details
The user needs to correct imported date and time values that use US month/day order and display them in UK-style DD/MM/YYYY HH:MM. Cell formatting alone is not enough when WPS Spreadsheets or Excel has misread the underlying values.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has imported a column of US-formatted date and time values and needs to use or report them in UK date/time format without losing the correct chronological values.
- Observed behavior
- Imported dates remain in US order or become unusable because the source values were text or were interpreted as the wrong month/day; applying a display format does not repair incorrect underlying values.
First check whether the imported values are real date serial numbers or text. Select a cell and look at the formula bar: a real date appears as a date, while text stays exactly as imported. Also check whether the US values always use two-digit month, two-digit day, and four-digit year so you can choose a safe parsing method.
Rebuild Misread US Date-Time Text with a WPS Spreadsheets Formula
Use this when imported US date-time values are stored as text or were interpreted with the wrong month and day; it reconstructs a valid date and time value from the text.
This method works for text values in a fixed US pattern such as MM/DD/YYYY HH:MM. If the pattern uses single-digit months or days, use Text to Columns or set the correct date interpretation during import instead to avoid formula errors.
Select the column next to your imported US date-time data and insert a blank helper column in WPS Spreadsheets.
In the first helper cell, enter =DATE(VALUE(MID(A2,7,4)),VALUE(LEFT(A2,2)),VALUE(MID(A2,4,2)))+TIMEVALUE(MID(A2,12,5)) and press Enter.
Drag the fill handle down to apply the formula to every imported date-time cell.
Select the helper column, press Ctrl+1, click Custom under Category, enter dd/mm/yyyy hh:mm, and click OK.
Copy the helper column, right-click the original range, choose Paste Special > Values, then delete the helper column.
Format Existing Valid Date-Time Cells to UK Style
Use this when the imported values are already real date-time serial numbers and only the display looks US-style; this changes the visible format only.
Import US Date-Time Data with the Correct Date Interpretation
Use this before loading a CSV or text file so dates are interpreted correctly from the source and later formatting is not needed for repair.
Convert US Date-Time Imports to UK Format Directly in WPS Spreadsheets
Use WPS Spreadsheets to parse, rebuild, and format imported US date and time values as UK DD/MM/YYYY HH:MM. You can correct misread dates with a formula, apply a custom number format, or import with the right date interpretation in a familiar spreadsheet interface.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Spreadsheets, and open the workbook containing the imported US date-time column.
- 2. Inspect the date-time values: Select a data cell and check the formula bar to determine whether the value is text or a real date serial number.
- 3. Repair the values: If values are misread, use the helper-column DATE and TIMEVALUE formula described above, then fill it down the full column.
- 4. Apply the UK display format: Select the repaired column, press Ctrl+1, choose Custom, enter dd/mm/yyyy hh:mm, and click OK.
- 5. Save the workbook: Press Ctrl+S to save the corrected workbook; the .xlsx file remains compatible with Microsoft Excel.
Frequently Asked Questions
Why does changing the cell format not fix imported US dates?
Because a number format only changes how a value is displayed. If WPS Spreadsheets has already interpreted the US date as the wrong month/day or stored it as text, the underlying value must be repaired with a formula or by importing with the correct date interpretation before formatting.
Can I convert US date and time values in WPS Spreadsheets without Power Query?
Yes. Use a helper column with =DATE(VALUE(MID(A2,7,4)),VALUE(LEFT(A2,2)),VALUE(MID(A2,4,2)))+TIMEVALUE(MID(A2,12,5)) for fixed MM/DD/YYYY HH:MM text, then fill down and apply the custom format dd/mm/yyyy hh:mm.
What is the correct custom format for UK date and time in WPS Spreadsheets?
Select the cells, press Ctrl+1, choose Custom, and enter dd/mm/yyyy hh:mm. This displays day/month/year in two digits and time in hours:minutes.
How can I prevent US dates from being misread when importing a CSV into WPS Spreadsheets?
Use Data > Import Data > From Text/CSV, check the preview, and set the date column to the source MDY order or the appropriate locale during import. After loading, apply dd/mm/yyyy hh:mm for UK display.
Does the formula preserve the time portion?
Yes. The TIMEVALUE(MID(A2,12,5)) portion adds the time value to the reconstructed date, so the resulting cell contains a full date-time serial value.
