Excel LEFT Function for Dates: What It Returns

Formulas & functions · Updated November 19, 2026

Excel’s LEFT function can take a date, but it does not read the date as it appears on the screen: it reads the underlying date serial number. To extract characters from a date in a particular display format, convert it to text first with TEXT, as in =LEFT(TEXT(A2,"yyyy-mm-dd"),4), which returns the year as text when A2 contains a real Excel date.

Why LEFT behaves unexpectedly with dates

Excel stores most dates as numbers and uses a cell’s number format to show those numbers as calendar dates. For example, a cell might display January 15, 2024, while its underlying value in the standard 1900 date system is the serial number 45306. The display is a date; the stored value is numeric.

LEFT is a text function. When Excel receives a number for its text argument, it converts that number to text rather than using the cell’s visible date format. With a real date of January 15, 2024 in A2, =LEFT(A2,2) returns 45, the first two characters of the serial number 45306—not 01 for the month or 20 for the year.

This distinction explains a common misconception: applying a date format to A2 changes how the cell looks, but does not turn its value into a formatted text string for LEFT to read. The same principle applies to other number formats, such as currency and percentages. A value displayed as $12.50 is still numeric unless a formula converts it to text.

What LEFT does in Excel

LEFT returns a specified number of characters from the beginning of a text string. Its syntax is LEFT(text,[num_chars]). The second argument is optional; if you leave it out, Excel returns one character. If you request more characters than the string contains, LEFT returns the whole string.

The function counts characters from left to right. It does not identify date components, interpret separators, or know whether a two-digit substring represents a month, day, or year. That is why choosing the source text matters. A date written as 2024-01-15 begins with the year, while a date written as 01/15/2024 begins with the month.

In Excel for Microsoft 365 on Windows, the same formula behavior applies whether you enter a formula in the grid or use the formula bar. Excel for Mac and Excel for the web also support LEFT and TEXT; the formulas below work there as well. The key issue is the value in the cell, not the operating system.

Use TEXT to extract characters from a displayed date format

TEXT converts a number into text using a format code you specify. Nesting TEXT inside LEFT lets you control which date representation LEFT receives. For example, if A2 contains the real date January 15, 2024, the formula =LEFT(TEXT(A2,"yyyy-mm-dd"),4) first creates the text 2024-01-15 and then returns 2024. The result is text, even though it consists of digits.

Choose a format code that puts the component you need at the beginning. Common patterns include:

  • "yyyy-mm-dd" for a year-first date such as 2024-01-15.
  • "mm/dd/yyyy" for a month-first date such as 01/15/2024.
  • "dd-mmm-yyyy" for a day-first date such as 15-Jan-2024.

With January 15, 2024 in A2, =LEFT(TEXT(A2,"mm/dd/yyyy"),2) returns the text 01. That keeps the leading zero. If you instead want the month as a number for calculations, MONTH is more suitable: with the same date in A2, =MONTH(A2) returns the number 1.

In Excel date format codes, lowercase m represents a month when used in a date pattern. In a time pattern it can represent minutes, so context matters when combining dates and times. TEXT also returns text by design: a year produced as "2024" may need conversion if a later calculation requires a numeric year.

Choose the right method for the result you need

Before writing a formula, decide whether you need characters, a date component, or a differently displayed date. These are related but not identical tasks. LEFT is appropriate when you specifically need a substring, such as the first four characters of a year-first date. Date functions are usually clearer when the goal is to retrieve a calendar component.

  • Need the year as a number? With a real date in A2, =YEAR(A2) returns 2024.
  • Need the month as a number? With a real date in A2, =MONTH(A2) returns 1 for January.
  • Need the day of the month? With a real date in A2, =DAY(A2) returns 15.
  • Need a text fragment in a chosen layout? Use TEXT to format the date, then LEFT to take the required characters.

YEAR, MONTH, and DAY return numeric values that are easier to use in arithmetic, sorting, or comparisons than text fragments. For instance, the numeric month 1 can be compared with other numbers; the text value "01" is a string. Use LEFT when the requested output really is text, such as a four-character year code or the prefix of a date label.

Check whether the cell contains a real date or text

A cell that looks like a date may contain either a real Excel date or text that resembles one. A real date is numeric and can generally be changed to another date display through number formatting. Text is a sequence of characters; changing its number format usually does not transform it into a genuine date.

If A2 contains the text 2024-01-15, rather than a real date value, =LEFT(A2,4) returns 2024 directly because those characters are already present in the text. By contrast, if A2 contains a real date, that same formula may return the first four digits of its serial number. The identical-looking cell content can therefore produce different results depending on how it was imported or entered.

Imported files are a frequent source of mixed data. Some rows may be recognized as dates while others remain text, especially when the source uses a different regional date order. To inspect a cell, select it and look at the formula bar and the Number Format controls on the Home tab. A number format can offer clues, but it is not a definitive test on its own; a formula such as ISNUMBER can help check whether the stored value is numeric. If a date is text, convert it deliberately before using date functions, taking care that Excel interprets the order of day and month correctly.

Practical steps for a reliable formula

For a date column, a dependable approach is to verify the data type, choose an output format, and then build the extraction formula. For example, suppose A2 contains the real date January 15, 2024 and you need a four-character year for a text label. The formula =LEFT(TEXT(A2,"yyyy-mm-dd"),4) returns 2024 as text.

  1. Select a sample date cell and determine whether it is a real date value or date-like text.
  2. Decide whether the output should be text (such as "01") or a number (such as 1).
  3. If it should be text, set the date pattern inside TEXT so the desired part comes first.
  4. Use LEFT with the number of characters required, then check the result against a few known dates, including dates with single-digit months or days.
  5. Fill the formula down only after confirming that the source column is consistent.

To enter the formula in Excel for Microsoft 365 on Windows, select the result cell, type the formula in the formula bar or directly in the cell, and press Enter. You can use the fill handle to copy it down. On Mac, the formula is the same; keyboard shortcuts for filling or confirming may differ by keyboard settings, but no Mac-specific formula is required. On the web, enter and copy the formula in the same way using the browser interface.

Common problems and how to fix them

The result is a serial-number prefix. This usually means LEFT received a real date directly. Wrap the cell in TEXT with an explicit date pattern before extracting characters, or use YEAR, MONTH, or DAY for a numeric component.

The year or month is missing a leading zero. LEFT cannot preserve a zero that was not included in the text it received. Use a fixed-width pattern such as "mm" or "dd" inside TEXT when the output needs two characters. For example, with January 15, 2024 in A2, =TEXT(A2,"mm") returns the text 01.

The formula returns an unexpected month or day. Check the chosen format pattern and the source data’s interpretation. A date stored as text in day/month/year order can be ambiguous if opened under a different regional convention. Do not assume that a displayed string has been parsed as the intended date.

The result is text when a calculation expects a number. That is normal when using LEFT or TEXT. If the purpose is arithmetic or date comparisons, prefer date functions that return numbers. Avoid converting text back casually when the original data may contain ambiguous date strings.

Dates differ by a day between workbooks. Excel workbooks can use different date systems. Most use the 1900 date system, but a workbook can use the 1904 system; the same serial can then correspond to a different calendar date. This is an issue when transferring raw serial values between workbooks, not a reason to use LEFT. Keep dates as dates and verify the workbook’s date system if copied values appear shifted.

Frequently Asked Questions

Can LEFT extract the year from an Excel date?
Yes, if you first format the date as text with TEXT. For a real date in A2, =LEFT(TEXT(A2,"yyyy-mm-dd"),4) returns the year as text. Use YEAR instead if you need a numeric year.

Why does LEFT return 45 for my date?
Your cell likely contains a real date whose serial number begins with 45. LEFT reads the stored number converted to text, not the date format shown in the cell. Use TEXT to specify a date layout first.

Can I use LEFT on a date that is already text?
Yes. If A2 contains the text 2024-01-15, =LEFT(A2,4) returns 2024. Confirm that all rows use the same text layout before filling the formula down.

Is LEFT or YEAR better for getting a year?
YEAR is generally better when the source is a real Excel date and you need a number for calculations. LEFT is useful when you specifically need text, or when you are extracting from a consistently structured text string.

Does changing the date format make LEFT work?
No. Changing a cell’s number format changes its display, not its underlying value. Use TEXT in the formula to create formatted text that LEFT can read.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.