Excel Extract Part of Date: How To

To extract part of a date in Excel, use the DAY, MONTH, and YEAR functions to pull the day, month, or year as a number, or use TEXT to get it as a formatted string. For example, =YEAR(A1) returns the year of a date in cell A1, and =TEXT(A1,”mmmm”) returns the full month name.

Formula

The core formulas are straightforward:

• Year: =YEAR(serial_number) returns a 4-digit year (e.g., 2025).
• Month: =MONTH(serial_number) returns a number 1–12 (e.g., 3 for March).
• Day: =DAY(serial_number) returns a number 1–31 (e.g., 14).
• Text: =TEXT(serial_number, "format") returns the date part as text using a format like “yyyy”, “mmm”, “dddd”.

In all cases, serial_number is a reference to a cell containing a date, or a date entered directly (e.g., DATE(2025,3,14)). Excel stores dates as serial numbers (1 = January 1, 1900), but you rarely need to know that for extraction.

Worked Example

Suppose cell A1 contains the date 2025-03-14 (March 14, 2025).

1. =YEAR(A1) → 2025
2. =MONTH(A1) → 3
3. =DAY(A1) → 14
4. =TEXT(A1,"mmmm") → “March”
5. =TEXT(A1,"dddd") → “Friday”

These results are what you see in Excel when you type the formulas into separate cells.

Why Extract Date Parts?

You often need just one component of a date for sorting, filtering, grouping, or building custom labels. For example, you might want to:

• Group sales data by month for a pivot table.
• Create a report that shows only the year.
• Build a filename like “Report_2025-03.xlsx”.
• Check if a date falls on a weekend using =WEEKDAY(date,2).

Extracting parts keeps your original data intact while letting you analyze it in new ways.

How to Choose the Right Function

Your choice depends on what you want to do with the result:

• Numeric output – Use YEAR, MONTH, DAY when you need a number for calculations (e.g., comparing months or adding 1 to a year).
• Text output – Use TEXT when you need a readable label like “Mar” or “Friday” for charts or reports.
• Date arithmetic – If you need the first day of the month, use =DATE(YEAR(A1),MONTH(A1),1) instead of extracting just the month.
• Week number – Use =WEEKNUM(A1) to get the ISO week number, not DAY or TEXT.

Remember: YEAR, MONTH, and DAY return numbers, while TEXT returns a string. If you try to do math on a TEXT result, Excel may treat it as text and give errors.

Common Problems and Mistakes

Several issues trip up users:

• Date stored as text – If your cell contains “2025-03-14” as text (left-aligned by default), YEAR/MONTH/DAY will return #VALUE!. Fix by converting to a real date using =DATEVALUE(A1) or the Text to Columns wizard.
• Wrong format codes – TEXT uses format codes like “yyyy” (lowercase) and “mmmm”. Using “YYYY” also works, but “MM” gives the month number, not the name. Check the code list in Excel’s help.
• Locale differences – In some regions, dates are formatted as DD/MM/YYYY. TEXT may return unexpected results if you use a format code that assumes US order. Always test with your own regional settings.
• Leading zeros – DAY and MONTH return numbers without leading zeros. If you need “03” for March, use =TEXT(A1,"mm") instead.

Alternatives to the Basic Functions

For more complex extractions, consider:

• WEEKDAY – Returns the day of the week as a number (1–7). Combine with TEXT to get the name.
• DATEDIF – Calculates the difference between two dates in years, months, or days. Useful when you need the number of complete months between dates.
• EOMONTH – Returns the last day of the month. Combine with DAY to get the number of days in a month.
• Power Query – For large datasets, you can split a date column into separate columns using the UI, which is faster than writing formulas.
• Flash Fill – If you just need a quick one-time extraction, type the desired output in the next column and press Ctrl+E. Flash Fill can often guess the pattern.

Frequently Asked Questions

How do I extract the month name in Excel?

Use =TEXT(A1,"mmmm") for the full month name (e.g., “March”) or =TEXT(A1,"mmm") for the abbreviated name (e.g., “Mar”). If you want the month as a number, use =MONTH(A1).

Why does my YEAR function return #VALUE!?

This usually means the cell contains text that looks like a date, not a true Excel date. Convert the text to a date using =DATEVALUE(A1) or use the Text to Columns feature (Data tab → Text to Columns → Date).

Can I extract the day of the week as a name?

Yes. Use =TEXT(A1,"dddd") for the full name (e.g., “Friday”) or =TEXT(A1,"ddd") for the abbreviation (e.g., “Fri”). For a number, use =WEEKDAY(A1).

How do I extract the year from a date and keep it as a number?

Use =YEAR(A1). It returns a 4-digit number. If you need it as text (e.g., to concatenate with other strings), wrap it in TEXT: =TEXT(A1,"yyyy").

What is the difference between TEXT and YEAR/MONTH/DAY?

YEAR, MONTH, and DAY return numeric values that you can use in calculations. TEXT returns a string formatted exactly as you specify. For example, =YEAR(A1) returns 2025, while =TEXT(A1,"yyyy") returns “2025” as text. Choose based on whether you need to do math.

Can I extract the date part from a datetime value?

Yes. If you have a cell with both date and time (e.g., 2025-03-14 09:30), use =INT(A1) to get the date as a serial number, then format it as a date. Or use =DATE(YEAR(A1),MONTH(A1),DAY(A1)) to get a clean date value.

Related

• Drone Delivery: How It Works, Where It’s Available, What’s Next
• ESP32 Handheld Arcade & Console Emulator Build
• Pokemon Unite Shutdown Date: Is It Ending?

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2