How to AutoFill Dates in Excel

Need to create a list of dates in Excel? Whether you're scheduling projects, building timesheets, or planning reports, it’s quick and easy to autofill dates in Excel - no manual typing required.

In this guide, you’ll learn how to generate date lists using the Fill Handle, Autofill options, and formulas for dynamic lists. We’ll cover consecutive dates, weekdays only, custom intervals, and even advanced formula techniques. Let’s dive in.

Note: the dates in this guide are formatted day-month-year.

Watch the AutoFill Dates Video

 

Get the Excel Example File

Quick Methods to Autofill Dates

1. Consecutive Days with the Fill Handle

Start with a single date in a cell (e.g., 1/1/2025 in cell B5).
Then:

• Hover over the bottom-right corner of the cell until you see the black cross.
• Drag the Fill Handle down (or across) to fill the dates sequentially.

Notice the tooltip in the image above showing you what date you’re up to.

Tip: If your dates are next to a column of data, double-clicking the Fill Handle will autofill down to match the adjacent column's length.

2. Repeat Dates

If you want the same date repeated as you left click and drag down, hold the CTRL key to copy the date. You should see a small plus (in red for effect in the image below) indicating that you’re copying instead of filling the dates:

3. Troubleshooting Autofill Issues

If Excel repeats the same date instead of increasing it:

• Your cells may be formatted as Text instead of Date.
• Fix it: Go to Home tab → Number Format → Choose Date.

If dragging doesn’t work at all:

• The Fill Handle may be disabled.
• To enable it: Go to File tab → Options → Advanced → under Editing Options → Check Enable Fill Handle and Cell Drag-and-Drop.

Autofill Options: Weekdays, Months, and Years

Want to skip weekends or fill by month?

1. Type your starting date (e.g., 6/1/2025).
2. Drag the Fill Handle.
3. Click the Autofill Options button that appears in the bottom right:

4. Choose from any of the options, including:
   • Fill Weekdays: Skips weekends.
   • Fill Months: Same day each month (e.g., 5th Jan, 5th Feb).
   • Fill Years: Same day each year.

Quick trick: Save a click by right-clicking and dragging to access the same Autofill menu when you release.

Create Date Patterns (Weekly, Bi-Weekly, Custom)

1. Weekly Dates (e.g., Every Monday)

• Enter two consecutive Mondays (e.g., 6/1/2025 and 13/1/2025).
• Select both cells and drag the Fill Handle.
• Excel identifies the pattern and fills the column accordingly.

2. Fortnights (Every Two Weeks)

Same as above, but with a 14-day gap between dates (e.g., 6/1/2025 and 20/1/2025).

3. Custom Schedules (e.g., every two days)

• Type the first few dates manually (e.g., 6/1, 8/1, 10/1).
• Select all the cells containing the pattern and drag the Fill Handle.
• Excel extends the pattern automatically.

Automate Date Lists with Formulas

Manual dragging is great - but formulas can make your spreadsheets dynamic and auto-update. Let’s start with some easy examples and step up to full automation.

1. Continuous Dates

• B5: 1/1/2025
• B6: =B5+1
• Drag the formula down.

Change the date in B5, and the entire list updates.

2. Fortnights with a Formula

• C5: 1/1/2025
• C6: =C5+14
• Copy down for every two weeks.

Tip: Adjust the days added to change the increment.

End of Month and First of Month Dates

Excel makes it easy to generate a list of the last or first day of each month using a combination of built-in date functions. Let’s break down how these work, so you understand exactly what each formula is doing.

Note: These formulas require dynamic arrays, available in Excel 365 or Excel 2021 onwards.

1. Last Day of Each Month (2025)

=EOMONTH(DATE(2025, SEQUENCE(12), 1), 0)

What this does:

• SEQUENCE(12) generates the numbers 1 through 12—representing each month of the year.
• DATE(2025, SEQUENCE(12), 1) returns the first day of each month in 2025 (e.g., 01/01/2025, 01/02/2025, etc.).
• EOMONTH(..., 0) takes each of those dates and returns the end of that same month. The 0 means “stay in the same month.”

More on these functions:

• SEQUENCE Function tutorial
• DATE Function tutorial
• EOMONTH Function tutorial

2. Last Day of Each Month (2 Years)

=EOMONTH(DATE(2025, SEQUENCE(24), 1), 0)

This is the same idea, but now SEQUENCE(24) gives us 24 months—so this will generate a list of end-of-month dates for 2 years, starting in January 2025 and ending in December 2026.

You might wonder why SEQUENCE(24) doesn’t result in repeating January to December 2025.

The key is the month argument in the DATE function which is being fed the values 1 to 24.

Excel allows the month argument in DATE(year, month, day) to go beyond 12—and it automatically rolls over to the correct month and year. So:

• DATE(2025, 1, 1) = Jan 1, 2025
• DATE(2025, 12, 1) = Dec 1, 2025
• DATE(2025, 13, 1) = Jan 1, 2026
• DATE(2025, 24, 1) = Dec 1, 2026

That’s why DATE(2025, SEQUENCE(24), 1) gives you 24 unique months, progressing from January 2025 through December 2026.

3. First Day of Each Month

=DATE(2025, SEQUENCE(24), 1)

Here’s what’s happening:

• SEQUENCE(24) generates the month numbers 1 to 24, again giving us a 2-year range.
• DATE(2025, SEQUENCE(24), 1) when SEQUENCE is used for the month argument of DATE it returns a list of the first date for each month for 24 months starting January 1^st, 2025 through December 1^st, 2026

Weekday-Only Lists (Skip Weekends and Holidays)

Use the WORKDAY.INTL function to allow for custom weekends and holidays:

=WORKDAY.INTL(DATE(2024,12,31), SEQUENCE(21), 1, D53:D54)

• SEQUENCE(21) gives 21 workdays added to the end of the year 2024.
• 1 tells Excel to skip Saturday and Sunday.
• D53:D54 is a reference to a range of holiday dates.

Custom Work Schedules (e.g., Tue–Fri Only)

Need a list of dates that follow a non-standard workweek, like Tuesday through Friday only? The WORKDAY.INTL function gives you powerful flexibility to define your own custom schedule—perfect for shift work, part-time rosters, or unique business hours.

=WORKDAY.INTL(DATE(2025,1,1), SEQUENCE(31), "1000011")

What each part does:

DATE(2025,1,1)

This sets your starting date—January 1st, 2025 in this case. You can change this to any date or reference a cell.

SEQUENCE(31)

This generates a list of 31 numbers (1 to 31), which represent how many workdays to calculate. Each number tells Excel to find the next valid workday based on your custom schedule.

"1000011"

This is the custom weekend argument. It’s a 7-digit binary string that tells Excel which days are non-working days.

Each digit represents a day of the week starting from Monday (left) to Sunday (right):

• 1 = non-working day
• 0 = working day

So "1000011" translates to:

Day	Value	Meaning
Monday	1	Not working
Tuesday	0	Working
Wednesday	0	Working
Thursday	0	Working
Friday	0	Working
Saturday	1	Not working
Sunday	1	Not working

Result: The formula returns 31 dates where only Tuesday to Friday are included. Mondays and weekends are skipped.

First Monday (or Tuesday etc.) of Each Month

If you need a list of the first Monday (or any other weekday) of each month—for meetings, reporting deadlines, or recurring tasks—this clever Excel formula will generate it automatically.

To return the first Monday of each month in 2025:

=DATE(2025, SEQUENCE(12), 7) - WEEKDAY(DATE(2025, SEQUENCE(12), 1) - 1, 3)

What Each Part Does:

SEQUENCE(12)

Generates the numbers 1 through 12, representing each month of the year. This makes your formula scalable and dynamic.

DATE(2025, SEQUENCE(12), 7)

This returns the 7th day of each month in 2025:

• DATE(2025, 1, 7) = 7 Jan 2025
• DATE(2025, 2, 7) = 7 Feb 2025
  ... and so on.

Why the 7th? Because the first Monday in any month is guaranteed to occur on or before the 7th. We’ll subtract just the right number of days from the 7th to land on the first Monday.

DATE(2025, SEQUENCE(12), 1) - 1

This returns the last day of the previous month. For example:

• DATE(2025, 1, 1) - 1 = 31 Dec 2024
• DATE(2025, 2, 1) - 1 = 31 Jan 2025

WEEKDAY(..., 3)

The WEEKDAY function part:

WEEKDAY(DATE(2025, SEQUENCE(12), 1) - 1, 3)

gives you the weekday number of the last day of the previous month, where:

• 0 = Monday
• 1 = Tuesday
• ...
• 6 = Sunday

So this tells us how many days to subtract from the 7th of the current month to get to the first Monday.

Entire Formula Together:

=DATE(2025, SEQUENCE(12), 7) - WEEKDAY(DATE(2025, SEQUENCE(12), 1) - 1, 3)

This subtracts the weekday number of the last day of the previous month from the 7th, which lands you on the first Monday of the current month.

Want a Different Day?

You can tweak the formula to get the first occurrence of any weekday:

Desired Day	Change -1 to:
Monday	-1 (as in example)
Tuesday	-2
Wednesday	-3
Thursday	-4
Friday	-5
Saturday	-6
Sunday	-7

Pro Tip: This works because WEEKDAY(..., 3) always returns 0 (Monday) to 6 (Sunday), making math on weekdays predictable and flexible.

Next Steps

You now know how to fill dates in Excel quickly - whether it's a daily list, a workweek schedule, or a formula-driven calendar that updates automatically. Want to keep learning?

If you want to learn more hidden tricks and tips like this that elevate your Excel skills to ninja level, so you can boost productivity, enhance data analysis, and increase career opportunities, check out my Excel Expert course.