Function Problems

How to Fix Excel LEFT, RIGHT, and MID Functions Returning Date Serial Numbers

Elise Williams Oct 16, 2026 872 views

Question details

Users attempting to extract characters from a date cell using LEFT, RIGHT, or MID functions receive numerical serial values instead of the formatted date text.

How to Fix Excel LEFT, RIGHT, and MID Functions Returning Date Serial Numbers

Product: Excel
Device & OS: not provided
Scenario: Using text string extraction formulas on cells containing dates to pull out specific parts like the year, month, or day.
Observed behavior: The formulas return portions of a 5-digit number (the underlying date serial number) rather than the expected visual date characters.

Before you start

Verify that your source cells are formatted as dates, and determine whether you need the extracted component to behave as text or as a numerical value for future calculations.

Solution 1Recommended

Use the TEXT Function to Convert Dates to Strings

The most effective way to extract parts of a date as text is by converting the date serial number into a formatted text string using the TEXT function.

Because Excel stores dates as continuous serial numbers starting from January 1, 1900, text functions like LEFT, RIGHT, and MID read this raw number instead of the visual format. By wrapping your date reference in the TEXT function, you force Excel to read the formatted string.

Select the target cell

Click on the cell where you want the extracted text to appear.

Enter the TEXT formula

Type `=TEXT(A2, "yyyy")` to extract the full 4-digit year as a text string. Replace 'A2' with your actual target cell reference.

Combine with LEFT, RIGHT, or MID (Optional)

If you need an exact character count from a complex date format, nest the TEXT formula inside your extraction function, for example: `=LEFT(TEXT(A2, "mm/dd/yyyy"), 2)` to extract just the month.

Apply the formula

Press Enter to execute the formula and drag the fill handle to apply it to the rest of your column.

Use the TEXT Function to Convert Dates to Strings

Formatting Flexibility: You can change the "yyyy" argument to "mm" for the month, "dd" for the day, or "mmm" for the abbreviated month name (e.g., Jan).

Solution 2

Use Built-in Date Functions (YEAR, MONTH, DAY)

If you are trying to extract the year, month, or day to use in further mathematical calculations, use Excel's dedicated date functions.

Seamless Data Processing

Extract and Format Dates Effortlessly with WPS Spreadsheet

WPS Spreadsheet provides comprehensive support for TEXT, YEAR, MONTH, and DAY functions. It handles date serial numbers identically to Excel, meaning all your standard formulas work flawlessly without requiring modifications.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your problematic date cells.
2. Select your destination cell: Click on the blank cell where you want the correctly formatted date component to be extracted.
3. Enter the formula: Type `=TEXT(A2, "yyyy-mm-dd")` or use `=YEAR(A2)` depending on your data needs.
4. Press Enter to execute: Hit Enter to evaluate the formula and see your date extracted correctly instead of a raw serial number.

Fully compatible with Microsoft Excel formulas and date serial formatsIntuitive formula autocomplete to help you accurately write TEXT and YEAR functionsLightweight application that processes heavy datasets and complex formulas smoothlyFree to download and use for all your daily data management and analysis tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel turn my dates into 5-digit numbers?

Excel stores dates sequentially as serial numbers starting from January 1, 1900. For instance, the number 45000 represents a specific date in 2023. When you use text-based functions like LEFT or RIGHT, they read this underlying 5-digit number instead of the visual format displayed in your spreadsheet.

Can I permanently convert date serial numbers to text strings?

Yes. You can use a `=TEXT(A2, "mm/dd/yyyy")` formula to create formatted text, then copy the result cells, right-click, and select 'Paste as Values'. This permanently overrides the formulas with raw text strings that can be processed by LEFT, RIGHT, or MID functions natively.

Does WPS Office handle date serial numbers differently than Microsoft Excel?

No, WPS Office uses the exact same serial number logic for dates as Microsoft Excel (the 1900 date system). This guarantees seamless compatibility, ensuring that your TEXT, YEAR, and MID formulas migrate without breaking.

Excel & Data Problems