'%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 Convert Text Dates and Times for Excel Pivot Table Grouping
Question details
The user needs to convert dates and times imported as text into actual date and time values to enable grouping in Excel PivotTables.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing database data where dates and times are formatted as text, preventing the PivotTable grouping feature from functioning.
- Observed behavior
- Dates and times imported as text cannot be grouped in Excel PivotTables until they are properly converted into real date and time values.
Before converting your data, ensure you select the entire column containing the text dates or times and verify that there are no adjacent columns that might be overwritten if the data separates.
Use Text to Columns to Convert Dates and Times
The Text to Columns wizard is the most efficient built-in feature to force Excel to recognize text strings as real date and time values.
This method applies a built-in data transformation tool that directly overwrites the text strings in your column with proper serial date numbers, fixing the PivotTable grouping issue immediately.
Click the letter of the column containing the dates or times formatted as text to highlight the entire column.
Navigate to the 'Data' tab on the Excel ribbon and click the 'Text to Columns' button.
Leave the data type as 'Delimited' and click 'Next' twice until you reach Step 3 of 3 in the wizard.
Under the 'Column data format' section, select the 'Date' radio button. Choose the format that matches your text data from the dropdown (for example, 'MDY' for Month-Day-Year).
Click 'Finish'. If you are converting time values, simply running the wizard and clicking 'Finish' without changing settings will usually convert the text into usable times.
Convert Text Using Excel Formulas
Use the DATEVALUE or TIMEVALUE functions if you want to keep your original text column intact and perform a dynamic conversion in a new column.
Group PivotTable Dates Instantly with WPS Spreadsheet
WPS Spreadsheet seamlessly handles database imports and provides intuitive Text to Columns features, making it incredibly simple to convert and group date or time data in PivotTables.
- 1. Open your dataset: Launch WPS Spreadsheet and open your .xlsx file containing the text dates.
- 2. Access Text to Columns: Select the column with the text dates, go to the 'Data' tab, and click 'Text to Columns'.
- 3. Convert the format: Choose 'Delimited', click 'Next' to reach Step 3, select 'Date' (e.g., MDY), and click 'Finish'.
- 4. Create and group PivotTable: Select your entire dataset, click 'Insert' > 'PivotTable', and drag your newly converted date values to rows to easily group them by months or years.
Frequently Asked Questions
Why can't I group dates in my Excel PivotTable?
If your dates are imported or formatted as text, Excel treats them as distinct string values rather than a continuous timeline. Grouping requires the data to be formatted as actual date serial numbers.
How do I refresh the PivotTable after converting the text dates?
Click anywhere inside your PivotTable, navigate to the 'PivotTable Analyze' tab on the ribbon (or 'Options' in older versions), and click the 'Refresh' button. Alternatively, right-click inside the PivotTable and select 'Refresh'.
Does Text to Columns work for time values stored as text?
Yes. Selecting the affected column, opening the Text to Columns wizard, and simply clicking 'Finish' without changing any settings forces Excel to re-evaluate the data, usually converting the text into standard time values.
What formula should I use if my text strings contain both dates and times?
You can use the VALUE function. For example, type '=VALUE(A2)' in an adjacent column. You will get a decimal number. Format this new cell using a custom Date and Time format (e.g., 'mm/dd/yyyy hh:mm') to display both properly.
