Make a List of Dates With SEQUENCE in Excel
By ExcelReady TeamUpdated 6 min read
The short answer
To make a sequence of dates in Excel, use SEQUENCE with a date as the start: =SEQUENCE(7,1,DATE(2024,3,1),1) lists seven days from 1 March 2024. The step is in days, so 7 gives weekly dates. Format the cells as dates if you see numbers. For dates between two cells, use =SEQUENCE(B2-B1+1,1,B1). SEQUENCE needs Excel 2021, 2024 or Microsoft 365.
Key takeaways
- SEQUENCE(rows, columns, start, step) makes a date list when start is a date and step is days.
- Dates are numbers to Excel, so format the result as a date if it shows values like 45352.
- End date minus start date plus 1 gives the number of rows for a list between two dates.
- For months, put SEQUENCE inside DATE: =DATE(2024,SEQUENCE(12),1).
- In older Excel, type one date and drag the fill handle, or use Home > Fill > Series.
On this page+
To make a sequence of dates in Excel, give the SEQUENCE function a date as its start value. =SEQUENCE(7,1,DATE(2024,3,1)) lists seven days in a row, starting on 1 March 2024, and changing one number changes the whole list. This guide shows daily, weekly and monthly lists, dates between two cells, workdays only and a calendar grid, plus the fill handle method for older Excel.
How SEQUENCE makes a list of dates#
SEQUENCE returns a list of numbers that go up by a fixed step. Its syntax is:
=SEQUENCE(rows, [columns], [start], [step])| Argument | What it means | Default |
|---|---|---|
| rows | How many rows to fill | Required |
| columns | How many columns to fill | 1 |
| start | The first number | 1 |
| step | How much to add each time | 1 |
The trick is that Excel stores dates as numbers. 1 March 2024 is the serial number 45352, and each day adds 1. So if the start is a date and the step is 1, SEQUENCE counts days. The article on why Excel dates are numbers explains this in plain terms.
SEQUENCE needs Excel 2021, Excel 2024 or Microsoft 365. The result spills into the cells below, so they must be empty. For the function's other uses, see the full SEQUENCE function guide.
How to list consecutive days#
Type the formula in one cell and press Enter:
=SEQUENCE(7,1,DATE(2024,3,1))DATE(2024,3,1) builds 1 March 2024 from a year, a month and a day. The formula fills seven rows with 1 March to 7 March. If you see 45352, 45353 and so on, the cells just need a date format. Select them and pick Short Date from the number format box on the Home tab, or press Ctrl+1 (Mac: ⌘1) and choose Date.
Use DATE or a cell for the start, not a date typed in quotes. "3/1/2024" means 1 March in one country and 3 January in another.
How to list dates between two dates#
To fill every date from a start to an end, work out the number of rows from the dates. With the start in B1 and the end in B2:
=SEQUENCE(B2-B1+1,1,B1)| A | B | |
|---|---|---|
| 1 | Start | 1-Mar-2024 |
| 2 | End | 10-Mar-2024 |
B2-B1 is 9, because subtracting dates gives the days between them. The +1 includes the end date, so the list has 10 rows: 1 March to 10 March. Change B2 to 31-Mar-2024, and the list grows to 31 rows on its own. More on date subtraction is in how to count the days between dates.
Weekly, monthly and yearly date sequences#
Change the step for weekly dates. For months and years, put SEQUENCE inside DATE instead, because months don't have a fixed number of days.
| List | Formula | First results |
|---|---|---|
| Every Monday for 6 weeks | =SEQUENCE(6,1,DATE(2024,3,4),7) | 4-Mar, 11-Mar, 18-Mar |
| First day of each month in 2024 | =DATE(2024,SEQUENCE(12),1) | 1-Jan, 1-Feb, 1-Mar |
| Last day of each month in 2024 | =DATE(2024,SEQUENCE(12)+1,0) | 31-Jan, 29-Feb, 31-Mar |
| 1 January for five years | =DATE(SEQUENCE(5,1,2024),1,1) | 2024, 2025, 2026 |
The month-end formula uses a neat DATE rule: day 0 of a month is the last day of the month before. So DATE(2024,2,0) is 31 January, and DATE(2024,3,0) is 29 February, because 2024 is a leap year.
Format month lists with a custom format like mmm yyyy to show "Jan 2024". The cell still holds the full date, so formulas that use it keep working.
How to list workdays only#
SEQUENCE always counts every day. To keep Monday to Friday, filter out the weekends with WEEKDAY:
=FILTER(SEQUENCE(14,1,DATE(2024,3,4)),WEEKDAY(SEQUENCE(14,1,DATE(2024,3,4)),2)<6)The first SEQUENCE lists 14 days from Monday 4 March 2024. WEEKDAY(date,2) returns 1 for Monday up to 7 for Sunday, so <6 keeps weekdays. The result is 10 dates: 4 to 8 March and 11 to 15 March. FILTER also needs Excel 2021 or later.
How to build a calendar grid with SEQUENCE#
Give SEQUENCE both rows and columns to fill a grid. Seven columns make a week per row:
=SEQUENCE(6,7,DATE(2024,3,1)-WEEKDAY(DATE(2024,3,1),2)+1)The start value finds the Monday on or before 1 March 2024. That day is a Friday, so WEEKDAY returns 5, and the start becomes 26 February. The grid fills six weeks, Monday to Sunday. Format it with the custom format d to show only the day numbers.
For headers above the grid, =TEXT(SEQUENCE(1,7,DATE(2024,2,26)),"ddd") spills Mon, Tue, Wed and so on across one row. To reorder any list of dates, see SORT and SORTBY.
How to make a date sequence in older Excel#
Excel 2019 and older don't have SEQUENCE, but the fill handle works in every version:
- Type the first date, such as 1-Mar-2024, in a cell.
- Drag the small square at the bottom-right corner of the cell down.
- Click the Auto Fill Options button that appears and choose Fill Days, Fill Weekdays, Fill Months or Fill Years.
For an exact end date, use Home > Fill > Series. Choose Columns, Date, a date unit (Day, Weekday, Month or Year), a step value and a stop value. The result is fixed values, not a formula, so it won't update when the start date changes.
Why is my date sequence wrong?#
Most problems have a quick fix:
- Numbers instead of dates. Apply a date format to the spilled cells.
- #SPILL! error. Cells below the formula aren't empty. Clear them. The #SPILL! error guide lists every cause.
- One day short. You used
B2-B1for the rows. Add 1 to include the end date. - #VALUE! error. The start or end cell holds text that Excel can't read as a date. Re-enter it, or build it with DATE.
Practise it#
Date lists are a building block for schedules, timelines and trackers. Practise SEQUENCE and other modern Excel functions in a live spreadsheet: you type the formula and it checks it instantly. Then practise how Excel stores dates with DATE, YEAR, MONTH and DAY. Start with the exercise on this page.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
B1 holds the start date and B2 the end date. In D2, use SEQUENCE to list every date from B1 to B2, one per row. Use B2-B1+1 for the number of rows.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Dates | ||||
| 2 | |||||
| 3 | |||||
| 4 | |||||
| 5 | |||||
| 6 |
Tip: Rows: B2-B1+1. Columns: 1. Start: B1. You can leave the step out, because it defaults to 1.
Practise this in a lesson
Frequently asked questions
How do I create a list of dates in Excel automatically?
In Microsoft 365 or Excel 2021 and later, type =SEQUENCE(30,1,DATE(2024,3,1)) to list 30 days from 1 March 2024. In older versions, type the first date, then drag the fill handle down.
How do I make a list of dates between two dates?
Put the start date in B1 and the end date in B2, then use =SEQUENCE(B2-B1+1,1,B1). The +1 makes sure the end date itself is included.
Why does SEQUENCE show numbers instead of dates?
Excel stores each date as a serial number, and the spilled cells may not have a date format. Select the results and choose Short Date from the number format box on the Home tab.
How do I make a list of months in Excel?
Use =DATE(2024,SEQUENCE(12),1) for the first day of each month in 2024. Format it as mmm yyyy to show Jan 2024, Feb 2024 and so on.
How do I list only weekdays with SEQUENCE?
Wrap the list in FILTER and keep days where WEEKDAY with type 2 is below 6: =FILTER(SEQUENCE(14,1,B1),WEEKDAY(SEQUENCE(14,1,B1),2)<6).