Generate a Series of Dates with One Formula

Excel Formulas › Date & Time

365 / 2021

Need a column of dates every 7 days, every month, or every quarter? SEQUENCE builds the whole list from a single formula — no dragging.

Quick formula: Spill a run of weekly dates starting from a cell:
=B2+(SEQUENCE(6)-1)*7

SEQUENCE returns 1,2,3,…; subtracting 1 and multiplying by the step spaces the dates out.

Functions used (tap for the full reference guide):

SEQUENCEDATEEDATE

The example

Start on Jun 1, 2026 and list six weekly dates.

AB
1LabelValue
2Start date2026-06-01
3Count6
4Last date2026-07-06
5Span (days)35

The formula

Build the run from the start date and a weekly step:

=B2+(SEQUENCE(B3)-1)*7 // spills 6 dates, 7 days apart

How it works

How it works:

  1. SEQUENCE(B3) returns the numbers 1 through 6 as a spilled array.
  2. Subtracting 1 shifts them to 0–5 so the first date equals the start.
  3. Multiplying by 7 turns each step into a week; add it to the start date.
  4. The formula spills down automatically — no fill handle needed.

For monthly dates use =EDATE(B2,SEQUENCE(B3)-1) instead, which respects month lengths.

Try it: interactive demo

Interactive

Pick a count and a step in days — see how far the series reaches.

Variations

First of each month

EDATE steps by whole months from a starting first-of-month.

=EDATE(B2,SEQUENCE(12)-1)

Only weekdays

Wrap WORKDAY around SEQUENCE to skip weekends.

=WORKDAY(B2-1,SEQUENCE(B3))

Pitfalls & errors

SEQUENCE needs Excel 365 or 2021. In older Excel, drag =B2+7 down instead.

Format the spilled range as dates — SEQUENCE math returns plain serial numbers.

Practice workbook

📊
Download the free Generate a Series of Dates with One Formula practice workbook
Change the yellow start date, count, and step; the span recalculates. (Spill the series in your own copy.)

Frequently asked questions

Why use SEQUENCE instead of dragging?
One formula produces the entire list and updates instantly when the start, count, or step changes — nothing to re-fill.
How do I get month-end dates?
Use =EOMONTH(B2,SEQUENCE(12)-1) to spill the last day of each of the next 12 months.
Can I make the list go backward?
Yes — use a negative step, e.g. =B2-(SEQUENCE(6)-1)*7 for earlier dates.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Weeks between two dates · Last business day of month

Function references: SEQUENCEDATEEDATE