How to Change Excel Date and Time Format to 24/11/2009 9:32 PM in WPS Office Spreadsheets
Question details
The user needs to display date-time values as day/month/year followed by a 12-hour time with seconds, for example 24/11/2009 9:32:00 PM. Text values must be converted before a custom date-time format can take effect.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Formatting date-time values in a worksheet so they appear as dd/mm/yyyy h:mm:ss AM/PM for reporting or data entry.
- Observed behavior
- Date-time cells either display as text or use a different format, and the custom date-time format does not change the display until the cells are recognized as real dates.
Check whether the cells contain real date-time values or text. In WPS Spreadsheets, select the cells and look at the Number Format box; text values are often left-aligned, while real dates are right-aligned by default.
Convert text date-time values and apply the dd/mm/yyyy h:mm:ss AM/PM custom format
Use this solution when the date-time values are stored as text or when applying a custom format does not change the display.
In WPS Spreadsheets, custom date-time formats only affect cells that contain actual serial date-time values. The Text to Columns command can quickly convert text entries into real dates without retyping them.
Open your workbook in WPS Office Spreadsheets and select the column or cells that contain the date-time text values.
Click the Data tab, choose Text to Columns, and then click Finish in the dialog to convert the selected text into real date-time values.
Keep the cells selected, press Ctrl+1 or right-click and choose Format Cells, then select Custom in the Category list.
In the Type box, enter dd/mm/yyyy h:mm:ss AM/PM and click OK. The values now display like 24/11/2009 9:32:00 PM.
Apply the custom format directly to real Excel date-time cells
Use this solution when the values are already real date-time cells and you only need to change their display format.
Format Excel date-time values as dd/mm/yyyy h:mm:ss AM/PM in WPS Office Spreadsheets
WPS Office Spreadsheets can open your Excel workbook, convert text date-time values, and apply an Excel-compatible custom date-time format so cells display as 24/11/2009 9:32:00 PM.
- 1. Open your Excel file in WPS Office Spreadsheets: Launch WPS Office and open the workbook containing the date-time values you want to format.
- 2. Convert text values if needed: Select the date-time column, click Data > Text to Columns, and choose Finish to convert text to real dates.
- 3. Open the custom format dialog: Keep the cells selected, press Ctrl+1 or right-click > Format Cells.
- 4. Apply the dd/mm/yyyy h:mm:ss AM/PM format: Select Custom, type dd/mm/yyyy h:mm:ss AM/PM, and click OK.
- 5. Save the formatted workbook: Click Menu > Save or press Ctrl+S to keep the display format in your Excel-compatible file.
Frequently Asked Questions
Why does dd/mm/yyyy h:mm:ss AM/PM not change my date cells in WPS Spreadsheets?
Usually the cells contain text instead of real dates. Select the range, click Data > Text to Columns, and choose Finish to convert them. Then apply the custom format in Format Cells.
How do I type the custom format exactly for 24/11/2009 9:32:00 PM?
In Format Cells > Number > Custom, enter dd/mm/yyyy h:mm:ss AM/PM. This gives day/month/year, 12-hour time, seconds, and AM/PM.
Can I use Format Painter to convert text dates into this date-time format?
No. Format Painter only copies formatting. Convert text through Data > Text to Columns first, then use Format Painter to copy the custom date-time format.
Does WPS Office support Excel custom date-time format codes?
Yes. WPS Office Spreadsheets supports Excel-compatible custom number formats, including dd/mm/yyyy h:mm:ss AM/PM, and opens .xlsx and .xls files.
What is the difference between h:mm and hh:mm in a custom time format?
h:mm shows hours without a leading zero, while hh:mm shows two digits. Use h:mm for 9:32 and hh:mm for 09:32.
