How to Extract Year, Month, and Day in Excel
You need year, month, day, weekday, and quarter in their own columns for a pivot — but that's four or five separate formulas, and they all error on text dates. Here's why, and the one-click way.
Last updated: August 2026
| Before | After | |
|---|---|---|
| 2026-02-13 | → | 2026 | 2 | 13 | Friday | Q1 |
| 13/02/2026 (text) | → | 2026 | 2 | 13 | Friday | Q1 |
Why the formulas choke
To split a date you write a column each of YEAR, MONTH, and DAY, then TEXT(A2,"dddd") for the weekday and ROUNDUP(MONTH(A2)/3,0) for the quarter — four or five formulas to author, copy down, and paste as values. Worse, every one of them returns #VALUE! on a cell where the date is stored as text rather than a real Excel date, which is exactly how dates arrive from most exports.
The 1-click way — Tellsheet's Extract Date Parts
Select the date column and run Extract Date Parts. Tick the parts you want — year, month, day, weekday, quarter — and it writes each into its own column, ready to drop onto a pivot. It uses the same parser as Fix Dates, so it reads real and text dates alike where YEAR, MONTH, and DAY would error. No formulas to write, copy down, or paste as values. Pairs with Fix Dates and Date Difference.
Frequently asked questions
How do I extract year, month, and day?
Select the date column and run Extract Date Parts. It writes year, month, and day — plus weekday and quarter — each into its own column in one click.
Can it give the weekday and quarter?
Yes — a date like 2026-02-13 yields Friday and Q1 alongside the numeric parts, each in its own column.
Does it handle text dates?
Yes — it shares the Fix Dates parser, so text dates are read where YEAR/MONTH/DAY would simply error.
Why is the output pivot-ready?
Each part is a real column, so you can drop it straight onto a PivotTable's rows or columns with no helper formulas.
Related Excel guides
Split a date column in one click
Extract Date Parts writes year, month, day, weekday, and quarter into their own columns — from real or text dates, ready for pivots.
Get Tellsheet free See pricing