How to Create a Date Table / Calendar Table in Excel
Every PivotTable, Power Pivot, and Power BI model wants a proper date dimension — Year, Quarter, Month, ISO Week, Day of Week, Weekend, and fiscal periods. Excel has no button for it. Here's how to generate the whole calendar table between two dates in one click, fiscal years included.
Last updated: August 2026
| Two dates | Date table (new sheet) | |
|---|---|---|
| 2026-01-01 | → | Year 2026, Q1, Jan, Thursday, Week 1 |
| 2026-01-02 | → | Year 2026, Q1, Jan, Friday, Week 1 |
| 2026-01-03 | → | …, Saturday, Weekend |
Why Excel makes this hard
A real date dimension means one of two chores. Either you autofill a date series down thousands of rows and bolt on a dozen helper columns by hand — YEAR(), MONTH(), WEEKNUM(), TEXT() for the month and day names, an ISOWEEKNUM(), a weekend flag — or you drop into Power Pivot and write a DAX CALENDAR table. There's no built-in "generate a calendar table" button anywhere in Excel. And fiscal years make it worse: a fiscal year that starts in April needs its own offset formulas for both the year and the quarter, so most people just give up and use the calendar year.
The 1-click way — Tellsheet's Date Table / Calendar
Run Date Table / Calendar and type a start and end date (YYYY-MM-DD). Optionally set the fiscal start month (e.g. 4 for April) to add Fiscal Year and Fiscal Quarter columns. It writes one row per day with all the dimension columns — Date, DateKey (yyyymmdd), Year, Quarter, Month, Month Name, Day, Day of Week, ISO Week, and Weekend — to a new sheet. The range comes from the dates you type, not the selection, so nothing on your current sheet changes. Pairs with Extract Date Parts and Date Calculator.
Frequently asked questions
Does Excel have a generate-calendar button?
No — you autofill a date series and add YEAR/MONTH/WEEKNUM helper columns by hand, or write DAX CALENDAR. Date Table builds all of it from two dates.
Can it do fiscal years?
Yes — set the fiscal start month (e.g. 4 for April) and it adds Fiscal Year and Fiscal Quarter columns. Leave it blank for calendar year only.
Where does the range come from?
From the start and end dates you type, not the selection. One row per day is written to a new sheet.
Is the output ready for a PivotTable or Power Pivot?
Yes — it's a clean, one-row-per-day table with a DateKey, ready to mark as the date table in your data model.
Related Excel guides
Build a calendar table in one click
Date Table generates a full date dimension between two dates — Year, Quarter, Month, ISO Week, Weekend, and fiscal periods — on a new sheet.
Get Tellsheet free See pricing