How to Automatically Display Year and Quarter from a Date in Excel
Question details
The user wants to automatically calculate and display the quarter and year from a specific date in Excel.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing or analyzing time-based data where standard dates need to be categorized into specific quarters and years for reporting.
- Observed behavior
- Dates are currently formatted as standard dates (e.g., October 11, 2021), and the goal is to dynamically output them as quarter and year combinations (e.g., Q4 2021).
Ensure your date column is properly formatted as dates in Excel, and identify a blank area in your worksheet to set up a small reference table for the quarters.
Use a VLOOKUP Formula with a Reference Table
This method uses a lookup table for the start months of each quarter, making the formula clean, flexible, and easy to maintain.
By utilizing a reference table, you can easily change the naming conventions of your quarters in the future without editing the formula itself.
In a blank area of your worksheet (for example, cells K3 to L6), create a two-column table. In the first column, list the starting months of each quarter (1, 4, 7, 10). In the second column, list the corresponding quarter labels (Q1, Q2, Q3, Q4).
Select the cell where you want the combined quarter and year result to appear (e.g., B2). Type the formula: =VLOOKUP(MONTH(A2),$K$3:$L$6,2,TRUE)&" "&YEAR(A2), assuming A2 contains your date.
Press Enter to see the result. Click on the cell again, grab the fill handle (the small square at the bottom-right corner), and drag it down to apply this formula to the rest of your data column.
Calculate the Quarter Using the ROUNDUP Function
If you prefer not to build a separate reference table, you can calculate the quarter mathematically using Excel's ROUNDUP function.
Use WPS Spreadsheet to Quickly Extract Dates and Quarters
WPS Spreadsheet fully supports all standard Excel formulas, including VLOOKUP, ROUNDUP, MONTH, and YEAR, allowing you to seamlessly calculate quarters from dates just as you would in Microsoft Excel.
- 1. Open your file: Launch WPS Spreadsheet and open your existing workbook containing the date values.
- 2. Set up a lookup table: Create a quick 2-column table mapping the start months (1, 4, 7, 10) to their corresponding quarter labels.
- 3. Apply the function: Use the =VLOOKUP(...) formula exactly as demonstrated to instantly generate the Quarter and Year combination.
Frequently Asked Questions
Can I change the format to '2021-Q4' instead of 'Q4 2021'?
Yes. You simply reverse the order of the formula elements using the ampersand (&). For example: =YEAR(A2)&"-"&VLOOKUP(MONTH(A2),$K$3:$L$6,2,TRUE).
What if my company's fiscal year starts in April instead of January?
If your fiscal year doesn't align with the calendar year, you can easily adjust the starting months and quarter labels in your reference table. For an April start, map month 4 to 'Q1', month 7 to 'Q2', month 10 to 'Q3', and month 1 to 'Q4'.
Why am I getting an #N/A error when I copy the formula down to other rows?
This usually happens if you didn't lock the reference table range. Make sure you use absolute references (dollar signs, like $K$3:$L$6) in your VLOOKUP formula so the table boundaries do not shift downwards when you drag the formula.
Is there a way to extract just the quarter without displaying the year?
Yes, you can omit the year part of the formula entirely. Just use =VLOOKUP(MONTH(A2),$K$3:$L$6,2,TRUE) or ="Q"&ROUNDUP(MONTH(A2)/3,0) to get the quarter label alone.
