如何在MS Excel中从dd/mm/yy格式日期提取月份名称

Hey there! I totally get wanting to avoid cluttering your sheet with extra columns while converting that dd/mm/yy formatted date to a full uppercase month name. Let's solve this with a single, self-contained formula you can drop straight into cell A2.

The Working Formula #

=UPPER(TEXT(DATE(RIGHT(A1,2)+2000,MID(A1,4,2),LEFT(A1,2)),"mmmm"))

How It Breaks Down #

Let's walk through each part to make sure you understand why this works:

• LEFT(A1,2): Pulls the day value (13) from your 13/04/18 text string
• MID(A1,4,2): Grabs the month value (04) from the middle of the string
• RIGHT(A1,2)+2000: Extracts the last two digits of the year and adds 2000 to get 2018 (swap to +1900 if you're dealing with dates before 2000)
• DATE(...): Converts those three values into a proper Excel date object that the program can recognize
• TEXT(..., "mmmm"): Takes the converted date and outputs the full month name (e.g., "April")
• UPPER(...): Converts the month name to all uppercase to match your desired APRIL output

Bonus: If Your System Uses dd/mm/yy Natively #

If your Excel's regional settings are already set to recognize dd/mm/yy as the default date format, you can simplify this even further with:

=UPPER(TEXT(--A1,"mmmm"))

The double hyphen (--) converts the text-based date into a numeric date value that Excel can work with, then the rest follows the same logic as above. But since you mentioned the basic TEXT formula only worked for mm/dd/yy, the first nested formula is the more reliable, region-agnostic option.

内容的提问来源于stack exchange，提问作者Fero