LEFT Function in Excel for Dates: How to Extract Parts

Formulas & functions · Updated November 20, 2026

To use LEFT with a date in Excel, first turn the date into text in the format you want, then take characters from the left. For example, if A2 contains a real Excel date, =LEFT(TEXT(A2,"yyyy-mm-dd"),4) returns the year as text. This matters because Excel stores dates as serial numbers: LEFT does not automatically read the date as it appears on screen.

This guide covers Microsoft Excel for Microsoft 365 on Windows. The same formulas work in Excel for Mac and Excel for the web, though ribbon locations and some keyboard shortcuts differ. If you only need a date part for calculations, functions such as YEAR, MONTH, or DAY are usually better choices than LEFT. Use LEFT when you specifically need characters from a date string, such as a year prefix for a label or a text code.

What You’ll Need

  • An Excel worksheet with dates in a column, such as dates in A2:A20. Start with one date in A2 for the examples below.
  • A basic idea of whether the cells contain real Excel dates or text that looks like a date. You can often tell by selecting a cell: real dates are numeric values with a date display format, while imported text remains text.
  • A blank destination column for the extracted result. LEFT returns text, even when the characters it extracts look like a number.

Step-by-Step

  1. Check what is in the source cell. Select a date cell, such as A2, and look at the formula bar. A real Excel date may appear there in a date format, while the worksheet displays it according to the cell’s number format. Excel stores a real date as a serial number; changing the display format does not change that stored value. If the cell contains a text date such as 2025-03-14, the characters themselves are available to LEFT. This distinction determines which formula to use.
  2. Decide which characters you want. If the goal is to take the year from a date, choose a consistent text layout such as year-month-day. In that layout, the first four characters are the year. If you want a month or day, choose a layout that puts that part at the beginning, or use MID or RIGHT after formatting the date as text. Write down the intended output before creating the formula so you can check that the date order is correct.
  3. Convert a real date to text in a predictable format. If A2 contains the real date March 14, 2025, use this formula in B2. It formats the date as year-month-day and returns the first four characters, so the result is 2025 as text: =LEFT(TEXT(A2,"yyyy-mm-dd"),4). TEXT controls the character sequence LEFT sees. The formula does not alter A2 or its date value; it creates a text result in B2.
  4. Use a format that starts with the part you need. For the same real date in A2, this formula formats the date as month/day/year and takes the first two characters. The result is 03, representing March: =LEFT(TEXT(A2,"mm/dd/yyyy"),2). To take the day instead, put the day first in the TEXT format. With the same date in A2, this formula returns 14: =LEFT(TEXT(A2,"dd-mmm-yyyy"),2). The hyphens are separators in the output text; LEFT counts characters, including any separators that fall within the requested number.
  5. Enter the formula in the result column. Click B2, type the appropriate formula, and press Enter. In Excel for Microsoft 365 on Windows, you can also click the formula bar after selecting B2. Check the result against the date in A2 before copying the formula down. If your date is not March 14, 2025, the output will differ, but its shape should still match the requested year, month, or day.
  6. Fill the formula down for other dates. Select B2 and point to the small square at its lower-right corner, called the fill handle. Drag it down the column, or double-click the fill handle when the neighboring source column has continuous data. Excel adjusts A2 to A3, A4, and so on. You can also copy B2, select the destination cells, and paste. On Windows, Ctrl+C and Ctrl+V copy and paste; on Mac, use Command+C and Command+V.
  7. Check whether the result should be text or a number. LEFT always returns text. A result such as 2025 may look numeric but is text, which can affect sorting, comparisons, and arithmetic. If you need the year as a number, a date-part function is clearer. With a real date in A2, =YEAR(A2) returns the numeric year 2025. For a month number or day number from the same date, use =MONTH(A2) or =DAY(A2); these return 3 and 14, respectively. Those are date calculations rather than character extraction.
  8. Handle text dates separately. If A2 already contains the literal text 2025-03-14, the first four characters are the year, so this formula returns the text 2025: =LEFT(A2,4). This shortcut is appropriate only when the text is consistently arranged with the year first. If the source is a real date, use TEXT inside the formula instead of relying on how the cell happens to look on screen.
  9. Copy or use the result where needed. You can use the extracted text in labels, concatenate it with other text, or send it to another system that expects a particular character string. If you need to replace formulas with fixed results, copy the result cells and use Paste Special > Values. Keep the original date column if you may need to sort chronologically, calculate date differences, or change the output format later.

Common Mistakes

Applying LEFT directly to a real date. A formula such as =LEFT(A2,4) does not necessarily take the first four characters of the displayed date. Excel uses the stored numeric date serial when converting the value to text, so the answer may look like part of a serial number rather than the year shown in the cell. Wrap the date in TEXT with a deliberate format first.

Assuming the cell display controls the formula. Formatting A2 to show yyyy-mm-dd changes its appearance, not the value LEFT receives. Use =LEFT(TEXT(A2,"yyyy-mm-dd"),4) when you need the visible year text. This makes the intended character order explicit in the formula.

Using a format that starts with the wrong component. LEFT only takes characters from the beginning. If the TEXT format is mm/dd/yyyy, the first two characters are the month, not the year. Choose a format that puts the desired part first, and count separators if you need more than one component.

Confusing text with numbers. LEFT returns text, so a year extracted as 2025 may not behave like the numeric result of YEAR. If you need a number for a calculation, use YEAR, MONTH, or DAY instead of extracting characters. Avoid converting the LEFT result unnecessarily unless another formula specifically requires a number.

Misreading a date stored as text. Text dates may use different orders, such as day-month-year or month-day-year. The first four characters are only the year when the source text actually starts with a four-digit year. Check several source cells, especially after importing data from another region or application.

Tips

Use a fixed TEXT format such as yyyy-mm-dd when you want a predictable year-first string. It is easy to inspect and avoids ambiguity between common day/month and month/day arrangements. In a TEXT format, yyyy represents a four-digit year, mm a two-digit month, and dd a two-digit day. Keep separators in the format if they help make the output readable, but remember that LEFT counts them as characters.

Choose LEFT for text handling, not for ordinary date analysis. For example, use YEAR to group dates by year, MONTH to create a month number, and DAY to get a day number. Those functions return numeric results that are more suitable for calculations and sorting as numbers. A LEFT-based result is useful when a destination needs a string, such as a four-character year prefix.

Test the formula on dates with different months and days before filling it down. A date such as March 4 can reveal whether a format keeps leading zeros: mm produces 03, while m produces 3. If you request two characters from a format that does not pad the month, the second character may be a separator or another part of the date rather than a digit.

Keep the original date in its own column. A real date can be sorted chronologically and used in date arithmetic; a text fragment is not a replacement for that value. Give the result column a clear heading such as Year text or Month text so other spreadsheet users know it is an extracted string, not necessarily a numeric date component.

Troubleshooting

The result is a strange number or only part of a serial. The source is probably a real date being passed directly to LEFT. Use TEXT to set the character layout, as in =LEFT(TEXT(A2,"yyyy-mm-dd"),4). Confirm that the referenced cell is the date you mean and that the TEXT pattern puts the requested component first.

The formula displays literally instead of calculating. Check that the cell is not formatted as Text. Change the cell format to General from Home > Number Format, then edit the formula and press Enter. Also check that the formula begins with an equals sign and that Show Formulas has not been switched on. On Windows, Ctrl+` toggles Show Formulas; on Mac, the shortcut may depend on the keyboard layout, so use Formulas > Show Formulas if available in the ribbon.

You see #VALUE! or an unexpected result. Verify that A2 contains a valid Excel date if the formula uses TEXT. Imported values may be text in an unrecognized date order, or may contain extra spaces. Check the source data and convert it to real dates if you need date functions. If it is intentionally text, use LEFT directly only after confirming its characters and layout.

The month or day has the wrong number of digits. Use a two-character format such as mm or dd when you need a leading zero. For example, use mm to represent March as 03. If you use m, March is represented as 3, so taking two characters from a longer date string can include the next separator instead.

The formula works in one row but not another. Look for inconsistent source types or formats. A column may mix real dates with text dates, or use more than one date order. Check several cells and standardize the data before filling the formula down. Also make sure the formula references the correct row; relative references should normally change from A2 to A3 as you fill down.

Frequently Asked Questions

Can I use LEFT on a date in Excel?
Yes, but it extracts characters from text, not a date component from Excel’s internal date value. For a real date, use TEXT to establish the character format first. For calculations, YEAR, MONTH, or DAY is generally the better choice.

How do I extract just the year with LEFT?
With a real Excel date in A2, use =LEFT(TEXT(A2,"yyyy-mm-dd"),4). It returns a four-character year, such as 2025, as text. If you need a numeric year, use =YEAR(A2) instead.

How do I get a month with LEFT?
Format the date so the month comes first, then take two characters. With a real date in A2, =LEFT(TEXT(A2,"mm/dd/yyyy"),2) returns a two-digit month such as 03. Use MONTH(A2) if you need the numeric month value 3.

Why does LEFT return the wrong year when the date looks correct?
The cell’s display format does not change its stored value. If A2 is a real date, LEFT(A2,4) can read the serial number rather than the displayed year. Put TEXT around A2 and specify the desired order, such as yyyy-mm-dd.

Does LEFT return text or a number?
LEFT returns text. This applies even when the result contains only digits. Use YEAR, MONTH, or DAY for numeric date parts, or convert the text only when there is a clear reason to do so.

Will these formulas work in Excel for Mac or the web?
Yes. LEFT, TEXT, YEAR, MONTH, and DAY work in current Excel for Mac and Excel for the web as well as Excel for Microsoft 365 on Windows. Ribbon locations and keyboard shortcuts can differ, but the formulas shown do not need to change.

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.