Convert CSV Date, Time, Time Zone, and Status Fields in Excel
Question details
Convert the date and time portion of a CSV field that also contains a time-zone label into a usable Excel date-time value, while keeping status and description data in separate fields.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A CSV record includes date, time, and a time-zone label in one field, followed by status and description fields.
- Observed behavior
- The date-time text needs to be separated from the time-zone label and converted from text into an Excel date-time serial value.
Confirm that the date and time occupy the first 19 characters of the imported field, such as YYYY-MM-DD HH:MM:SS. This method extracts the displayed date and time only; it does not convert the value between time zones.
Extract the first 19 characters and convert them to an Excel date-time
Use this method when the combined CSV field is already in cell A1 and starts with a consistent date-time value followed by a time-zone label.
The formula removes the trailing time-zone text by taking the first 19 characters, then converts the remaining date-time text into Excel's numeric date-time value.
In an empty column beside the imported field, select the first result cell and enter =VALUE(LEFT(A1,19)). Replace A1 with the cell that contains the original date-time and time-zone text.
Press Enter, then drag the fill handle from the bottom-right corner of the formula cell down through the remaining CSV records.
Select the converted values, open Home > Number Format, and choose a date-time format, or select More Number Formats and use a format such as yyyy-mm-dd hh:mm:ss.
To retain the label, use a separate column with =TRIM(MID(A1,20,LEN(A1))) and keep it as text. Do not treat the label as a time-zone conversion unless you also have a known numeric offset and conversion rule.
Split imported CSV fields into date-time, status code, and description columns
Use this approach when the CSV file has not yet been imported or when multiple values were placed into one worksheet column.
Convert CSV Date-Time Text to Usable Values with WPS Spreadsheets
WPS Spreadsheets can import CSV data, separate delimited fields, and convert the date-time portion of a combined date, time, and time-zone text field with the same LEFT and VALUE formula workflow.
- 1. Open the CSV file: In WPS Spreadsheets, select Menu > Open and choose the CSV file. Confirm the delimiter and column preview in the import options.
- 2. Separate fields if required: Select the combined data column, then use Data > Text to Columns and select the delimiter used in the CSV.
- 3. Convert the date-time text: Select an empty cell beside the date-time field and enter =VALUE(LEFT(A1,19)), adjusting A1 to match the source cell.
- 4. Format the converted values: Select the formula results and choose Home > Number Format > More Number Formats, then apply a date-time display such as yyyy-mm-dd hh:mm:ss.
Frequently Asked Questions
Why does the formula use the first 19 characters of the CSV field?
A standard date-time string such as 2024-01-31 14:25:00 contains 19 characters. LEFT(A1,19) excludes text that follows it, including a time-zone label.
Does =VALUE(LEFT(A1,19)) convert the time zone to my local time?
No. It converts only the extracted date and time into a spreadsheet date-time value. Keep the time-zone label in another column and apply a separate offset calculation only when the source offset and destination time zone are known.
How can I keep the time-zone text after extracting the date and time?
In another column, enter =TRIM(MID(A1,20,LEN(A1))). This returns the text after the first 19 characters, which can preserve the time-zone label.
What should I do if the CSV status and description are imported into one column?
Select the combined column and use Excel Data > Text to Columns. Choose Delimited, select the delimiter used in the file, and assign the resulting columns to Status Code and Description.
Why does Excel show a number after using the VALUE formula?
Excel stores dates and times as serial numbers. Select the result cells, then use Home > Number Format to apply a date-time format so the values display as dates and times.
