How to Convert Date to Quarter and Year in Excel

To convert a date to its corresponding quarter and year in Excel, follow these steps:

1. Open Excel and enter the dates you want to convert in a column, for example, in column A.
2. In the adjacent column (column B), enter the following formula to get the quarter for each date:

= "Q" & CEILING(MONTH(A1)/3,1) & " " & YEAR(A1)

Replace "A1" with the cell containing the date you want to convert. This formula first calculates the quarter by dividing the month by 3 and rounding up using the CEILING function. Then, it concatenates the "Q" text, quarter number, a space, and the year.

3. Press Enter to get the quarter and year for the first date.
4. Drag the fill handle (the small square in the bottom-right corner of the cell) down to apply the formula to the other dates in the column.

That's it! You have successfully converted the dates to their corresponding quarters and years in Excel.

Example

Below is an example with step-by-step instructions on converting dates to quarters and years:

1. Enter the following dates in column A:

A1: 01/15/2021
A2: 05/25/2021
A3: 09/10/2021
A4: 12/15/2021

2. In cell B1, enter the following formula:

= "Q" & CEILING(MONTH(A1)/3,1) & " " & YEAR(A1)

3. Press Enter. You will see "Q1 2021" in cell B1, which is the quarter and year for the date in cell A1.
4. Drag the fill handle in cell B1 down to cells B2, B3, and B4 to apply the formula to the other dates.

You should now see the following results:

B1: Q1 2021
B2: Q2 2021
B3: Q3 2021
B4: Q4 2021

These are the corresponding quarters and years for the dates in column A.