Ever wanted to get a set of dates, but don’t want to manually type? Here are three ways to insert dates in Excel automatically.

Option1: Enter a start date and Drag down

This is the easiest option if you just want a handful of dates. Just type in your starting date in a cell. Click in the bottom right corner of cell and drag down to get the next consecutive dates automatically.

Here is a quick demo of how to do this:

Insert just weekdays only

While dragging the dates, you can ask Excel to just insert weekdays only. This is helpful for creating dates for a project tracker or planner or other kinds of spreadsheets. To do this, after dragging your dates, click on the “options” button that appears at the bottom and select “Fill Weekdays Only” option.

See this demo:

Fill Dates in Months

You can also fill dates by Months. For this, just type the first date, drag down and use the “options” button to select “Fill Months” option. See this demo:

Automatic PayDays (or any other dates really!)

We can use the “dragging” mechanism to fill any kind of arbitrary dates too. Say, you want to see all the Pay Days in 2025. Type in the very first Pay Day (first Wednesday of the calendar year for example) and then, in the cell underneath, write the formula =cellabove+14 (replace cellabove with the actual address of the cell). Then drag this new cell down as far as you want.

See this quick demo:

Option 2: Using the “Fill Series” Secret Menu

Excel also offers a fairly powerful and easy way to fill dates if you want to get a large series of dates for a work project or spreadsheet. This is called “Fill Series” menu. This menu is hidden (buried really) but super helpful. Here is the step by step process:

1. Type the very first date of your series of dates in a cell.
2. Select a range big enough for all your dates. Tip: If you have a large series of dates to fill, just select all the cells in the column until end.
3. Press the keyboard shortcut sequence ALT H FI S or Go to File Ribbon > Fill > Fill Series button
4. Select “Date” and specify the “date unit” from the choices – Day, Weekday, Month or Year.
5. Enter the “step value” and “stop value” (tip: Use Step Value of 7 to get days filled by week, 14 for fortnight)
6. Click “OK” to see the magic. The dates are filled by Excel automatically.

See this quick GIF to understand the whole process.

Related: Learn about fill-series option in Excel

Option 3: Using SEQUENCE Formula

This one is for hardcore Excel fans and people who love to automate things. We can use the “new” SEQUENCE function of Excel to auto generate dates from any starting point until any end point. The formula is really easy to use and offers a ton of flexibility when it comes to building date trackers, project sheets or even financial models.

Let me share two simple yet powerful examples:

SEQUENCE Dates Example 1: All Dates in Year 2025

To generate all the dates in Year 2025, go to an empty cell and type the below formula.

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

Syntax of SEQUENCE to get dates automatically

=SEQUENCE(number of days,,starting date, optional step value)

Tip: Use the step value of 14 to generate dates by fortnight.

Working Days only – SEQUENCE Example 2

Let’s say for an upcoming project you want to list all the working days only. But you don’t know when the project starts. So you want to keep the “starting date” flexible and generate next “n” working days.

Imagine the start date of the project in cell C4 and number of working days in C5.

We can use below SEQUENCE function to get all the working days in the project.

=WORKDAY.INTL(C4,SEQUENCE(C5))

Bonus Tip: How to Set “Custom” Weekend Types

We can use WORKDAY.INTL function to tell Excel when your weekend is. For example if your weekend is “Friday & Saturday”, you can use below syntax:
=WORKDAY.INTL(C4,SEQUENCE(C5),7)
Here 7 stands for Friday & Saturday weekend.

Why I love SEQUENCE() approach

Of the 3 techniques outlined here, SEQUENCE() based approach is my favorite.

• Flexible: Many real-world scenarios where I need dates are dynamic. The starting date, end date, step value and what I need (days / weekdays / weeks / months) all change. Using SEQUENCE() I can create a robust yet flexible auto listing of dates for my workbooks.
• Can be linked to other formulas: As SEQUENCE generates a dynamic spill range, I can use # to access the range and build other scalable and flexible formulas. For example, if I want to calculate depreciation schedules for next “n” months, I can do so easily. When “n” changes, I don’t need to adjust anything as both my dates (from SEQUENCE) and depreciation calculations auto adjust.
• Fewer errors: Spillable formulas like SEQUENCE mean, there is only one formula that produce all the results. This avoids crazy errors like inconsistent formulas or hard-coded values.
• Faster: Dynamic formulas like SEQUENCE() are really fast and scale well even when I need to list dates for next century!

Related: Learn how to use SEQUENCE and other Dynamic Functions in Excel

Take Caution when using SEQUENCE()

• SEQUENCE function doesn’t format the dates. So you must format the cells after (or beforehand) to see the correct date format. Else Excel will list the dates as 45658, 45659 for 1-Jan-2025, 2-Jan-2025 etc.
• SPILL Errors: As SEQUENCE will dynamically fill the cells if your spreadsheet doesn’t have enough space for the SEQUENCE() to fill all dates, it will throw SPILL error.
• Can’t be used in Excel Tables: SEQUENCE and other dynamic functions don’t work inside Excel tables. Read this page for more information and possible fixes.
• Need Excel 365 or Excel on Web: To use SEQUENCE function, you need Excel 365 or Excel on the Web as this is a new functionality and not supported in older versions of Excel.

Working with Dates in Excel – More Tips & Tricks

Dates are integral part of any spreadsheet and data analysis scenario. Please refer to below pages and resources to learn more about important Date functions and tricks.

• Working With Date & Time values in Excel – A quick primer
• Date Calculation Tips (future / past dates and times made easy)
• Using Power Query to auto-generate Dates
• Free 2025 Calendar & Planner Template (uses SEQUENCE)
• Calculate difference between two dates
• Highlight Due Dates in Excel automatically
• 3 Powerful & Essential Date functions in Excel

The post How to insert dates in Excel automatically appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Original source: https://chandoo.org/wp/how-to-insert-dates-in-excel-automatically/