Create a date sequence in Excel and auto fill date series

This tutorial demonstrates how to efficiently generate date lists in Excel using the new SEQUENCE function and the AutoFill feature. Previously, AutoFill was the primary method; however, the SEQUENCE function now offers a formula-based alternative. This tutorial explores both methods.

Generating Date Series in Excel

The quickest method for populating a column with dates is using AutoFill.

AutoFill for Date Series

Creating a daily incrementing date series is straightforward:

1. Input the starting date in the first cell.
2. Select the cell and drag the fill handle (the small square at the bottom right) down or across.

Excel automatically generates a series matching the initial date's format.

Generating Weekdays, Months, or Years

To create series of workdays, months, or years:

• Method 1: Generate a sequential date series (as above). Then, click the AutoFill Options button and select the desired option (e.g., Fill Months).
• Method 2: Enter the starting date, right-click the fill handle, drag, and release. A context menu appears allowing you to choose the series type (Fill Years, for example).

Generating Date Series with Specific Increments

To create a series with a custom step (e.g., every N days):

1. Input the starting date.
2. Right-click the fill handle, drag, and release.
3. Select Series from the menu.
4. In the Series dialog, choose the Date unit and set the Step value.
5. Click OK.

Generating Date Sequences with Formulas

The SEQUENCE function, available in newer Excel 365 versions, generates number sequences and, due to Excel's date serial number system, can easily create date series. For older Excel versions, use the AutoFill method.

Creating a Date Sequence

The SEQUENCE function arguments are:

SEQUENCE(rows, [columns], [start], [step])

• Rows: Number of rows.
• Columns: Number of columns.
• Start: Starting date (e.g., "8/1/2020" or DATE(2020, 8, 1)).
• Step: Increment (e.g., 1 for daily increments).

For a list of 10 dates starting August 1, 2020:

=SEQUENCE(10, 1, "8/1/2020", 1) or =SEQUENCE(10, 1, DATE(2020, 8, 1), 1)

Alternatively, reference cells for the number of dates, start date, and step:

=SEQUENCE(B1, 1, B2, B3)

The results will automatically spill into the required cells. Apply a Date format to the cells for correct display.

Generating Workday Sequences

Use WORKDAY or WORKDAY.INTL to generate workday sequences:

=WORKDAY(B2-1, SEQUENCE(B1)) (B1 = sequence size, B2 = start date)

Generating Month Sequences

For monthly increments:

=DATE(2020, SEQUENCE(12), 1) (Generates dates for 2020, starting on the 1st of each month)

To display month names only, use custom date formats like "mmm" or "mmmm".

For a sequence starting from a specific date, use EDATE:

=EDATE(B1, SEQUENCE(12, 1, 0)) (B1 = start date)

Generating Year Sequences

For yearly increments:

=DATE(SEQUENCE(10, 1, YEAR(B1)), MONTH(B1), DAY(B1)) (B1 = start date, 10 = number of years)

Generating Time Sequences

Since times are decimal fractions of a day, use SEQUENCE directly:

=SEQUENCE(10, 1, B1, 1/48) (B1 = start time, increments by 30 minutes)

Or use the TIME function for more control:

=SEQUENCE(B2, B3, B4, TIME(E2, E3, E4)) (B2=rows, B3=columns, B4=start time, E2=hours, E3=minutes, E4=seconds)

Creating a Monthly Calendar

Use SEQUENCE, DATEVALUE, and WEEKDAY to create a dynamic monthly calendar:

=SEQUENCE(6, 7, DATEVALUE("1/"&B2&"/"&B1) - WEEKDAY(DATEVALUE("1/"&B2&"/"&B1)) 1, 1) (B1 = year, B2 = month)

Use conditional formatting to hide dates from previous/next months.

This tutorial provides comprehensive methods for generating date sequences in Excel, catering to various needs and Excel versions. A practice workbook is available for download.

The above is the detailed content of Create a date sequence in Excel and auto fill date series. For more information, please follow other related articles on the PHP Chinese website!