# How to convert quarter text like "Q1 2026" to a date in Excel

Take the quarter number and the year and build the first day: `=DATE(RIGHT(A2,4),MID(A2,2,1)*3-2,1)`.

**Excel:** `=DATE(RIGHT(A2,4),MID(A2,2,1)*3-2,1)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Quarter | Quarter start |
| Q1 2026 |  |
| Q3 2026 |  |
| Q4 2025 |  |
| Q2 2027 |  |
| Q1 2027 |  |

Formula in B2, filled to B6: `=DATE(RIGHT(A2,4),MID(A2,2,1)*3-2,1)`

Result: 1/1/2026; 7/1/2026; 10/1/2025; 4/1/2027; 1/1/2027

## How it works

`MID(A2,2,1)` takes the quarter number, and times 3 minus 2 gives the quarter's first month: 1, 4, 7 or 10. `RIGHT(A2,4)` takes the year, and DATE builds a real date you can sort, chart and use in SUMIFS.

## Other ways

- `=EOMONTH(DATE(RIGHT(A2,4),MID(A2,2,1)*3,1),0)`: The quarter end instead of the start. (Last day of the quarter.)

## Common mistakes

- Other layouts need other positions. For "2026-Q1": `=DATE(LEFT(A2,4),RIGHT(A2,1)*3-2,1)`.
- Fiscal quarters need the fiscal start: Q1 of a fiscal year that starts in July begins in July of the previous calendar year.

## Questions

**How do I turn a date back into a quarter label?**

`="Q"&ROUNDUP(MONTH(B2)/3,0)&" "&YEAR(B2)`.

**How do I sort quarter labels in order?**

Sort by the converted date. As text, "Q1 2027" sorts before "Q4 2025".

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/convert-quarter-text-to-a-date