Function Problems

How to Convert Text with a Time Zone to Date and Time in Excel

Kushani Nimanthika Oct 10, 2026 869 views

Question details

The user needs to extract date and time information from a text string that includes a time zone suffix and convert it into a usable numeric date format.

Convert Excel Date and Time Text with a Time Zone

Product: Excel
Device & OS: not provided
Scenario: Processing exported data where date and time text strings include time zone abbreviations (e.g., EDT), complicating standard character-count extraction.
Observed behavior: Standard text extraction formulas based on fixed character positions fail because variable lengths, such as a single-digit hour, shift the position of the time zone suffix.

Before you start

Identify the time zone abbreviations in your dataset (such as EDT or PST) and ensure there is an empty column adjacent to your data to output the converted date and time values.

Solution 1Recommended

Use the TEXTBEFORE Function to Remove Time Zone Text

This method dynamically isolates the date and time by removing everything after the last space, circumventing issues with variable character lengths.

The TEXTBEFORE function is ideal for datasets where the time zone abbreviation is always the last word in the string. By targeting the final space in the cell, it reliably extracts the date and time regardless of whether the hour has one or two digits.

Select an output cell

Click on an empty cell next to your target data, such as cell B2.

Enter the TEXTBEFORE formula

Type the formula =TEXTBEFORE(A2," ",-1) into the formula bar and press Enter. This tells the spreadsheet to return all text before the last space.

Apply Date and Time formatting

Select the newly calculated cell, right-click, and choose 'Format Cells'. Navigate to Custom and input m/d/yyyy h:mm AM/PM to display the value correctly.

Fill the formula

Click and drag the small square at the bottom-right corner of the cell (the fill handle) downwards to apply this conversion to the rest of your data column.

Use the TEXTBEFORE Function to Remove Time Zone Text

Version Compatibility: The TEXTBEFORE function is available in newer spreadsheet versions. If your software returns a #NAME? error, use the SUBSTITUTE method instead.

Solution 2

Use the SUBSTITUTE Function for Direct Conversion

A highly compatible method that replaces the specific time zone text with nothing and forces the result into a numeric date value.

Efficient Data Processing

Seamlessly Convert and Format Data with WPS Office

WPS Spreadsheet offers powerful text manipulation functions, making it easy to clean, extract, and format exported date and time data without the hassle of manual editing.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your exported text data.
2. Input the formula: Select an adjacent blank column and enter =--SUBSTITUTE(A2," EDT","") to remove the time zone and convert to a value.
3. Apply custom formatting: Right-click the cell, select Format Cells, go to the Custom tab, and type m/d/yyyy h:mm AM/PM.
4. Batch process data: Double-click the fill handle on the formatted cell to instantly convert the entire column.

Fully compatible with Microsoft Excel formulas, functions, and formatting.Rich built-in functions like SUBSTITUTE and text manipulation tools to handle complex data.Intuitive Format Cells dialog for effortlessly setting custom date and time displays.Lightweight installation and completely free for standard data processing needs.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return a #VALUE! error?

This happens if the time zone text wasn't completely removed or if there are hidden, non-breaking spaces in the exported data. Try wrapping your target cell reference in a TRIM or CLEAN function to remove excess whitespace before conversion.

Can I convert the extracted text back into a true Excel serial date?

Yes, adding a double negative (--) before your SUBSTITUTE or text function forces the spreadsheet to evaluate the resulting text string as a numeric serial value, which can then be mathematically manipulated or formatted as a date.

How do I handle multiple different time zones in the same column?

Using the TEXTBEFORE(A2," ",-1) function is the best approach for mixed time zones, as it dynamically removes the last word (the time zone suffix) regardless of what the specific abbreviation is.

Excel & Data Problems