Excel 根据指定季度提取对应月份的技术问询

Got it, let's break this down. You already have your quarter number calculated in cell B3 using =ROUNDUP(MONTH(date)/3,0)—now you just need to map that quarter to its three corresponding month names in C4, D4, and E4. Here's how to do it with simple Excel formulas:

Step 1: Formula for the first month of the quarter (C4) #

This formula calculates the first month of the quarter, then converts it to the full English month name:

=TEXT(DATE(2000, (B3-1)*3 + 1, 1), "mmmm")

• (B3-1)*3 +1: For quarter 2, this gives (2-1)*3+1 = 4 (April). For quarter 1, it gives 1 (January), etc.
• DATE(2000, ..., 1): We use a dummy year (2000 works fine) because we only care about the month name, not the actual date.
• "mmmm": Formats the date to the full month name (use "mmm" if you want abbreviations like "Apr" instead).

Step 2: Formula for the second month of the quarter (D4) #

Just adjust the month offset to +2 instead of +1:

=TEXT(DATE(2000, (B3-1)*3 + 2, 1), "mmmm")

For quarter 2, this calculates month 5 → "May".

Step 3: Formula for the third month of the quarter (E4) #

Adjust the offset to +3:

=TEXT(DATE(2000, (B3-1)*3 + 3, 1), "mmmm")

For quarter 2, this calculates month 6 → "June".

Example Test #

If B3 returns 2 (Q2), then:

• C4 will show April
• D4 will show May
• E4 will show June

Pro tip: If you want to reference your original date cell instead of using a dummy year (e.g., your date is in A3), replace 2000 with YEAR(A3)—though it won't change the month name result, it's useful if you ever need to tie the date to the actual year later.

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