How to Convert YYYYMMDD to Date and Numeric Values to Time in Excel
Question details
The user needs to convert continuous numeric or text strings (such as 20200703 and 2254) into native Excel date and time formats.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing or pasting external data where dates and times appear as unformatted continuous strings instead of standard Excel date/time serial numbers.
- Observed behavior
- Dates display as numeric strings (YYYYMMDD) or show hash marks (####) when formatted incorrectly. Time values appear as raw numbers (e.g., 2254) instead of standard time displays (22:54).
Ensure your data represents valid dates and times; values that do not correspond to valid dates or are out of bounds may result in error hash marks (####) when formatted.
Convert YYYYMMDD to Date using Text to Columns
Use the built-in Text to Columns feature to quickly parse 8-digit numeric strings into standard Excel date formats.
This method is the most efficient way to convert an entire column of YYYYMMDD strings into actual date serial numbers without using complex formulas.
Click the column header (e.g., Column F) or highlight the specific cells containing the YYYYMMDD strings.
Go to the 'Data' tab on the Excel ribbon and click the 'Text to Columns' button.
Leave the data type as 'Delimited', click 'Next', and leave all delimiter checkboxes unchecked in step 2. Click 'Next' again to proceed to step 3.
In the 'Column data format' section, select the 'Date' radio button. Click the adjacent dropdown menu and choose 'YMD'.
Click 'Finish'. Your text strings will instantly convert into native Excel date formats.
Convert Numeric Strings to Time using the TIME Function
Use a combination of the TIME and MOD mathematical functions to convert a 3 or 4-digit number (like 2254) into a valid time format.
Convert Numeric Times Directly Using a VBA Macro
Automate the conversion of numeric values to time format directly in the selected cells without needing auxiliary columns.
Convert Dates and Times Seamlessly with WPS Spreadsheet
Handle imported data, convert YYYYMMDD formats, and apply complex TIME formulas effortlessly in WPS Office. It provides a robust, user-friendly interface that natively supports all advanced data parsing functions.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the document containing your unformatted dates or numeric time strings.
- 2. Use Text to Columns: Highlight the YYYYMMDD column, navigate to the Data tab, and select 'Text to Columns'.
- 3. Select YMD Date format: Follow the wizard to step 3, select 'Date', choose 'YMD' from the dropdown, and click Finish for instant conversion.
Frequently Asked Questions
Why do I see hash marks (####) after converting my dates?
Hash marks typically appear if the converted date is invalid, if it results in a negative date value, or simply if the column is too narrow to display the formatted date. Try double-clicking the column header border to auto-fit the width first.
Can I format YYYYMMDD as a date using a formula instead of Text to Columns?
Yes, you can use the DATE function combined with text extraction functions. For a value in cell A1, use the formula =DATE(LEFT(A1,4), MID(A1,5,2), RIGHT(A1,2)) to extract and combine the year, month, and day.
How does the formula =TIME(N3/100,MOD(N3,100),0) extract hours and minutes?
The TIME function requires three arguments: hours, minutes, and seconds. Dividing 2254 by 100 yields 22.54; Excel ignores the decimal for the hours argument, effectively using 22. The MOD function, MOD(2254,100), extracts the remainder of the division, which is exactly 54, providing the minutes.
