Excel Convert Date to Month Year Day

To convert a date to just the month, year, or day in Excel, use the MONTH, YEAR, and DAY functions, each returning a single number (e.g., =MONTH(A1) gives 6 for June). For a combined text like “June 2025,â€� use TEXT(A1,”mmmm yyyy”) or a formula with & and the functions. This article explains every common method, including how to handle dates stored as text and how to build custom formats.

Formula

The core formulas are:

• Month: =MONTH(date) returns 1–12.
• Year: =YEAR(date) returns a 4-digit year (e.g., 2025).
• Day: =DAY(date) returns 1–31.
• Text combination: =TEXT(date,"mmmm yyyy") returns “June 2025â€�.
• Manual combination: =MONTH(A1)&"-"&YEAR(A1) returns “6-2025â€�.

In the calculator above, t is the date serial number (Excel stores dates as numbers since January 1, 1900). The functions extract the parts directly from that serial.

Worked Example

Assume cell A1 contains the date March 15, 2023 (serial number 45000). Using the default value from the calculator:

1. =MONTH(45000) → 3
2. =YEAR(45000) → 2023
3. =DAY(45000) → 15

The calculator’s first output shows 3 (month), then 2023 (year), then 15 (day). If you want “March 2023â€�, use =TEXT(45000,"mmmm yyyy") → “March 2023â€�.

Why Excel Dates Are Numbers

Excel stores every date as a serial number: January 1, 1900 is 1, January 2, 1900 is 2, and so on. The date you see in a cell is just a number with a date format applied. This is why the MONTH, YEAR, and DAY functions work—they read the serial number and return the corresponding part. It also means you can do arithmetic: add 7 to a date to get a week later.

If you type a date as text (e.g., “June 5 2025â€� with spaces), Excel may not recognize it as a date. The functions will return a #VALUE! error. To fix, use =DATEVALUE(A1) to convert text to a serial number first, or use the Text to Columns wizard.

Using TEXT for Custom Formats

The TEXT function gives you full control over the output format. Common format codes:

• "yyyy" → 2025
• "yy" → 25
• "mmmm" → June (full name)
• "mmm" → Jun (abbreviation)
• "mm" → 06 (two-digit month)
• "m" → 6 (no leading zero)
• "dd" → 05 (two-digit day)
• "d" → 5

Combine them: =TEXT(A1,"mmm dd, yyyy") → “Jun 05, 2025â€�. Note that TEXT returns text, not a number, so you cannot use it in further date calculations. For sorting or filtering, keep the original date and use a separate formatted column.

Converting to Month Name Only

If you need just the month name (e.g., “Juneâ€�), use =TEXT(A1,"mmmm"). To get the month as a number, use =MONTH(A1). A common mistake is using =TEXT(A1,"mm") which returns text “06â€�, not the number 6. If you need a number, use MONTH.

For a month name that updates automatically when the date changes, TEXT is the best choice. For a static month name, you can copy and paste as values after using TEXT.

Common Problems and Fixes

• #VALUE! error: The cell contains text, not a date. Use DATEVALUE or re-enter the date.
• Wrong year (e.g., 1905): The cell contains a serial number but is formatted as text. Change format to Date.
• Month shows as 1 for January but you want “Janâ€�: Use TEXT with “mmm”.
• Day shows as 1 but you want “01â€�: Use TEXT with “dd”.
• Date is from a different system (1904 date system): Excel for Mac may use a different base date. Check File > Options > Advanced > “Use 1904 date systemâ€�. If enabled, serial numbers differ by 1462 days.
• Leading zeros lost: When combining with &, use TEXT to keep zeros: =TEXT(A1,"mm")&"-"&TEXT(A1,"yyyy") gives “06-2025â€�.

Alternatives: Pivot Tables and Power Query

If you have a column of dates and want to group by month or year, a Pivot Table is faster than writing formulas. Drag the date field into Rows, right-click, and choose “Group� then select Months and Years. This creates a grouped table without altering your source data.

Power Query (Get & Transform) also has date functions. In the Add Column tab, you can extract Year, Month, or Day directly. This is useful when importing data from external sources that treat dates as text.

Frequently Asked Questions

How do I convert a date to just the month and year in Excel?

Use =TEXT(A1,"mm yyyy") for a text result like “06 2025â€�, or =MONTH(A1)&"-"&YEAR(A1) for “6-2025â€�. For a real date that shows only month and year, apply a custom number format mm yyyy to the cell, but the underlying value remains the full date.

Why does my MONTH formula return 1 for January but the cell shows “Jan�?

Because MONTH returns a number (1) and the cell is formatted to display “Janâ€�. If you want the text “Janâ€�, use TEXT(A1,”mmm”) instead. The number is useful for calculations, the text is for display.

Can I convert a text date like “June 5, 2025� to month/year?

Yes. First convert to a real date with =DATEVALUE("June 5, 2025"), then apply MONTH, YEAR, or TEXT. If DATEVALUE fails, the text format may not be recognized—use the Text to Columns feature with the Date option.

How do I get the day of the week from a date?

Use =TEXT(A1,"dddd") for the full name (e.g., “Mondayâ€�) or =TEXT(A1,"ddd") for “Monâ€�. For a number (1=Sunday), use =WEEKDAY(A1).

What is the difference between MONTH and TEXT for month?

MONTH returns a number (1–12) that you can use in calculations. TEXT returns a string that is not numeric. For example, =MONTH(A1)+1 gives 7 for June, but =TEXT(A1,"m")+1 gives a #VALUE! error because text cannot be added.

How do I convert a date to a quarter (Q1, Q2, etc.)?

Use ="Q"&ROUNDUP(MONTH(A1)/3,0) which returns Q1 for months 1–3, Q2 for 4–6, etc. For a fiscal year that starts in April, adjust the formula accordingly.

Related

• Quiet Quitting Rules: Why People Do It
• Recover Deleted Pictures on Nikon Camera
• How to Make a Facebook Page: Complete Guide

in t | Date (as serial number) | - | 45000 | 1
out m | Month number | - | month(t) | 0
out y | Year number | - | year(t) | 0
out d | Day number | - | day(t) | 0