'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Filter Hong Kong Date Formats Correctly in Excel
Question details
The user needs to properly sort and filter imported Hong Kong date formats chronologically in Excel.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing external or CSV data containing dates in a specific regional format (d/m/yyyy hh:mm:ss AM/PM) and attempting to apply chronological filters.
- Observed behavior
- The imported dates are stored as text instead of numeric date values, preventing standard chronological filtering or sorting. Changing the cell format alone does not fix the issue.
Verify whether your dates are stored as text by selecting the cells and changing their number format to 'General'; if they do not change into a 5-digit serial number, they are stored as text and must be converted.
Convert Text to Dates Using Text to Columns
This is the fastest method to convert existing text-formatted dates into real date serial numbers directly within the worksheet.
The Text to Columns wizard can instantly parse text strings that resemble dates and convert them into the underlying numeric values Excel requires for chronological filtering.
Highlight the entire column containing the text-formatted Hong Kong dates.
Navigate to the 'Data' tab on the ribbon and click on 'Text to Columns'.
Choose 'Delimited' in the first step and click 'Next' twice to reach step 3 of the wizard.
Under 'Column data format', select the 'Date' radio button. From the adjacent dropdown menu, choose 'DMY' (Day-Month-Year) to match the Hong Kong format, and click 'Finish'.
Right-click the converted cells, select 'Format Cells', and apply a custom format such as 'd/m/yyyy hh:mm:ss AM/PM'. You can now apply standard filters.
Transform Data Using Power Query Locale
Best suited for recurring imports of CSV files where the source data formatting differs from your system's regional settings.
Extract Date and Time Using Formulas
A precise method when dates are embedded within complex text strings or when built-in parsing tools fail.
Easily Fix Date Formatting Issues in WPS Spreadsheet
WPS Spreadsheet offers powerful, identical data cleaning tools such as Text to Columns and custom formatting, ensuring seamless compatibility with Microsoft Excel files. Easily convert text strings to dates and apply chronological filters effortlessly for free.
- 1. Open your file in WPS: Launch WPS Spreadsheet and open your imported document containing the text dates.
- 2. Select the data: Highlight the entire column where the Hong Kong date formats are stored.
- 3. Access Text to Columns: Navigate to the 'Data' tab on the top menu ribbon and click 'Text to Columns'.
- 4. Complete the conversion: Follow the wizard, select 'Delimited', set the column data format to 'Date (DMY)', and click Finish to instantly convert.
Frequently Asked Questions
Why doesn't changing the cell format to 'Date' fix my filter?
Changing the cell format only alters how Excel displays underlying numbers. If the data was imported as text, modifying the visual format will not convert the text string into the mathematical date serial number that Excel requires for chronological filtering.
Should I use the 1904 date system to fix locale issues?
No. The 1904 date system is an old compatibility setting originally designed for early Mac versions of Excel. Enabling it shifts your workbook's date baseline by 4 years and 1 day, which will cause widespread date calculation errors rather than fixing text-to-date import problems.
How do I know if my dates are stored as text?
Select the cells in question and change their number format to 'General'. If the cell continues to display a standard date (e.g., 25/12/2023) instead of changing into a 5-digit number (like 45286), the data is currently stored as text.
What if the Text to Columns method fails?
If Text to Columns fails to convert the dates, your source data might contain invisible non-printable characters or hidden leading/trailing spaces. Use the formula =TRIM(CLEAN(A2)) first to strip away these hidden characters, then attempt the conversion again.
