*The tutorial shows how you can leverage the new SEQUENCE function to quickly generate a list of dates in Excel and use the AutoFill feature to fill a column with dates, workdays, months or years.*

Until recently, there has been just one easy way to generate dates in Excel - the AutoFill feature. The introduction of the new dynamic array SEQUENCE function has made it possible to make a series of dates with a formula too. This tutorial takes an in-depth look at both methods so that you can choose the one that works best for you.

When you need to fill a column with dates in Excel, the fastest way is to use the AutoFill feature.

Filling a column or row with dates that increment by one day is very easy:

- Type the initial date in the first cell.
- Select the cell with the initial date and drag the fill handle (a small green square at the bottom-right corner) down or to the right.

Excel will immediately generate a series of dates in the same format as the first date that you typed manually.

To create a series of workdays, months or years, do one of the following:

- Fill a column with sequential dates as described above. After that, click the
*AutoFill Options*button and pick the desired option, say*Fill Months*:

- Or you can enter your first date, right-click the fill handle, hold and drag through as many cells as needed. When you release the mouse button, a context menu will pop-up letting you select the needed option,
*Fill Years*in our case:

To auto generate a series of days, weekdays, months or years with a **specific step**, this is what you need to do:

- Enter the initial date in the first cell.
- Select that cell, right-click the fill handle, drag it through as many cells as needed, and then release.
- In the pop-up menu, choose
**Series**(the last item). - In the
*Series*dialog box, select the*Date unit*of interest and set the*Step value*. - Click OK.

In one of the previous tutorials, we looked at how to use the new dynamic array SEQUENCE function to generate a number sequence. Because internally in Excel dates are stored as serial numbers, the function can easily produce a date series too. All you have to do is to correctly configure the arguments as explained in the following examples.

To generate a sequence of dates in Excel, set up the following arguments of the SEQUENCE function:

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

*Rows*- the number of rows to fill with dates.*Columns*- the number of columns to fill with dates.*Start*- the starting date in the format that Excel can understand, like "8/1/2020" or "1-Aug-2020". To avoid mistakes, you can supply the date by using the DATE function such as DATE(2020, 8, 1).*Step*- the increment for each subsequent date in a sequence.

For example, to make a list of 10 dates starting with August 1, 2020 and increasing by 1 day, the formula is:

`=SEQUENCE(10, 1, "8/1/2020", 1)`

or

`=SEQUENCE(10, 1, DATE(2020, 8, 1), 1)`

Alternatively, you can input the number of dates (B1), start date (B2) and step (B3) in predefined cells and reference those cells in your formula. Since we are generating a list, the columns number (1) is hardcoded:

`=SEQUENCE(B1, 1, B2, B3)`

Type the below formula in the topmost cell (A6 in our case), press the Enter key, and the results will spill across the specified number of rows and columns automatically.

To get a series of working days only, wrap SEQUENCE in the WORKDAY or WORKDAY.INTL function this way:

WORKDAY(*start_date *-1, SEQUENCE(*no_of_days*))

As the WORKDAY function adds the number of days specified in the second argument to the start date, we subtract 1 from it to have the start date itself included in the results.

For instance, to generate a sequence of workdays starting on the date in B2, the formula is:

`=WORKDAY(B2-1, SEQUENCE(B1))`

Where B1 is the sequence size.

- If a start date is Saturday or Sunday, the series will begin on the next working day.
- The Excel WORKDAY function assumes Saturday and Sunday to be weekends. To configure custom weekends and holidays, use the WORKDAY.INTL function instead.

To create a series of dates incremented by one month, you can use this generic formula:

DATE(*year*, SEQUENCE(12), *day*)

In this case, you put the target year in the 1^{st} argument and day in the 3^{rd} argument. For the 2^{nd} argument, the SEQUENCE function returns sequential numbers from 1 to 12. Based on the above parameters, the DATE function produces a series of dates like shown in the left part of the screenshot below:

`=DATE(2020, SEQUENCE(12), 1)`

To display only the **month names**, set one of the below custom date formats for the spill range:

- mmm - short form like
*Jan*,*Feb*,*Mar*, etc. - mmmm - full form like
*January*,*February*,*March*, etc.

As the result, only the month names will appear in cells, but the underlying values will still be full dates. In both series in the screenshot below, please notice the default right alignment typical for numbers and dates in Excel:

To generate a date sequence that increments by one month and **starts with a specific date**, use the SEQUENCE function together with EDATE:

EDATE(*start_date*, SEQUENCE(12, 1, 0))

The EDATE function returns a date that is the specified number of months before or after the start date. And the SEQUENCE function produces an array of 12 numbers (or as many as you specify) to force EDATE to move forward in one-month increments. Please notice that the *start* argument is set to 0, so that the start date gets included in the results.

With the start date in B1, the formula takes this shape:

`=EDATE(B1, SEQUENCE(12, 1, 0))`

To make a series of dates incremented by year, use this generic formula:

DATE(SEQUENCE(*n*, 1, YEAR(*start_date*)), MONTH(*start_date*), DAY(*start_date*))

Where *n* is the number of dates you want to generate.

In this case, the DATE(year, month, day) function constructs a date in this way:

*Year*is returned by the SEQUENCE function that is configured to generate an*n*rows by 1 column array of numbers, starting at the year value from*start_date*.*Month*and*day*values are pulled directly from the start date.

For example, if you input the start date in B1, the following formula will output a series of 10 dates in one-year increments:

`=DATE(SEQUENCE(10, 1, YEAR(B1)), MONTH(B1), DAY(B1))`

After being formatted as dates, the results will look as follows:

Because times are stored in Excel as decimals numbers representing a fraction of the day, the SEQUENCE function can work with times directly.

Assuming the start time is in B1, you can use one of the following formulas to produce a series of 10 times. The difference is only in the *step* argument. As there are 24 hours in a day, use 1/24 to increment by an hour, 1/48 to increment by 30 minutes, and so on.

30 minutes apart:

`=SEQUENCE(10, 1, B1, 1/48)`

1 hour apart:

`=SEQUENCE(10, 1, B1, 1/24)`

2 hours apart:

`=SEQUENCE(10, 1, B1, 1/12)`

The screenshot below shows the results:

If you do not want to bother calculating the step manually, you can define it by using the TIME function:

SEQUENCE(rows, columns, start, TIME(*hour*, *minute*, *second*))

For this example, we'll input all the variables in separate cells like shown in the screenshot below. And then, you can use the below formula to generate a time series with any increment step size you specify in cells E2 (hours), E3 (minutes) and E4 (seconds):

`=SEQUENCE(B2, B3, B4, TIME(E2, E3, E4))`

In this final example, we'll be using the SEQUENCE function together with DATEVALUE and WEEKDAY to create a monthly calendar that will update automatically based on the year and month that you specify.

The formula in A5 is as follows:

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

**How this formula works:**

You use the SEQUENCE function to generate a 6 rows (the max possible number of weeks in a month) by 7 columns (the number of days in a week) array of dates incremented by 1 day. Hence, the *rows*, *columns* and *step* arguments raise no questions.

The trickiest part in the *start* argument. We cannot start our calendar with the 1^{st} day of the target month because we do not know which day of the week it is. So, we use the following formula to find the first Sunday before the 1^{st} day of the specified month and year:

`DATEVALUE("1/"&B2&"/"&B1) - WEEKDAY(DATEVALUE("1/"&B2&"/"&B1)) + 1`

The first DATEVALUE function returns a serial number that, in the internal Excel system, represents the 1^{st} day of the month in B2 and the year in B1. In our case, it's 44044 corresponding to August 1, 2020. At this point, we have:

`44044 - WEEKDAY(DATEVALUE("1/"&B2&"/"&B1)) + 1`

The WEEKDAY function returns the day of the week corresponding to the 1^{st} day of the target month as a number from 1 (Sunday) to 7 (Saturday). In our case, it's 7 because August 1, 2020 is Saturday. And our formula reduces to:

`44044 - 7 + 1`

44044 - 7 is 4403, which corresponds to Saturday, July 25, 2020. As we need Sunday, we add the +1 correction.

This way, we get a simple formula that outputs an array of serial numbers beginning with 4404:

`=SEQUENCE(6, 7, 4404, 1)`

Format the results as dates, and you'll get a calendar shown in the screenshot above. For example, you can use one of the following date formats:

*d-mmm-yy*to display dates like*1-Aug-20**mmm d*to display month and day like*Aug 20**d*to display only the day

Wait, but we aim to create a monthly calendar. Why do some dates of the previous and next month show up? To hide away those irrelevant dates, set up a conditional formatting rule with the below formula and apply the **white font** color:

`=MONTH(A5)<>MONTH(DATEVALUE($B$2 & "1"))`

Where A5 is the leftmost cell of your calendar and B2 is the target month.

For the detailed steps, please see How to create a formula-based conditional formatting rule in Excel.

That's how you can generate a sequence of dates in Excel. I thank you for reading and hope to see you on our blog next week!

Date sequence in Excel - formula examples (.xlsx file)

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Shared email templates

Category: Excel Tips

60+ professional tools for Excel

2019–2010 to do your daily work

2019–2010 to do your daily work

Incredible product, even better tech support…AbleBits totally delivers!

Deborah Bryant

Anyone who works with Excel is sure to find their work made easier

Jackie Lee

The best spent money on software I've ever spent!

Patrick Raugh

Ablebits is a fantastic product - easy to use and so efficient.

Debra Celmer

Excel is at its best now

Annie C.

I don't know how to thank you enough for your Excel add-ins

Jennifer Morningstar

Anybody who experiences it, is bound to love it!

Kumar Nepa

AbleBits suite has really helped me when I was in a crunch!

Nelda Fink

I have enjoyed every bit of it and time am using it

Christian Onyekachi Nwosu

It's the best $100 we've ever spent!

Mike Cavanagh

I love the program, and I can't imagine using Excel without it!

Robert Madsen

One word… WOW!

Dave Brown

Love the products!

David Johnston

It is like having an expert at my shoulder helping me…

Linda Shakespeare

Your software really helps make my job easier

Jeannie C.

Thanks for a terrific product that is worth every single cent!

Dianne Young

I love your product

Brad Gibson

Awesome!!!

Sheila Blanchard

## 20 responses to "How to create a sequence of dates in Excel and auto fill date series"

This is so helpful, thank you!

"Type the initial date in the first cell.

Select the cell with the initial date and drag the fill handle (a small green square at the bottom-right corner) down or to the right.

Excel will immediately generate a series of dates in the same format as the first date that you typed manuall'

I followed this step in excel for Android and it didn't populate the empty cells. I had to hit "auto fill" in the context menu so the function is there. So idk what to do. Any idea?

Excellent! I have a cell which has "2020-Jun-01". Now I need to increment it every year. Say, by Jun 1st this year it should change to "2021-Jun-01". Is this possible using your steps? Thanks in advance.

Hello!

An Excel formula can change the value only in the cell that it is pasted in. If you need to change the content on the cell that has some value in it, you’ll need to use a VBA macro.

How would you create a sequence of 20 same days then increment by 1 to create another 20 days and repeat for say 3000 days?

Hello!

If I understand your task correctly, the following formula should work for you:

=TRUNC(MOD((ROW(A1)-1)/20,3000))+$D$1

$D$1 - your date.

After that you can copy this formula down along the column.

Hi thank you for sharing all of this knowledge

I want to create a sequential number based on every time a row is entered, but create a sequence that uses YYYY-MM-SEQUENTIAL_NUMBER (example: 2021-05-001, 2021-05-002...). I was trying to do this with date created but failed! :-)

Is this possible?

Hello!

I’m sorry but your description doesn’t give me a complete understanding of your task. Correct me if I’m wrong, but I believe the formula below will help:

=CONCATENATE(YEAR(NOW()),"-",MONTH(NOW()),"-",TEXT(ROW(),"000"))

You can copy this formula down along the column.

How would you go about modifying the monthly calendar so that it's for a yearly one instead? (I.e. automatically updates based on specified year and formats properly as well)

i want names of months between 2 dates.

ex: start date: 1-feb-2021 end date: 1-aug-21

requirement: feb march apr may jun july aug

all months to be filled in one cell.

Hello!

I believe the following formula will help you solve your task:

=CONCAT(CHOOSE(SEQUENCE(DATEDIF(A1,A2,"M")+1,,MONTH(A1),1),"Jan ","Feb ","Mar ","Apr ","May ","Jun ","Jul ","Aug ","Sep","Oct ","Nov ","Dec "))

You can learn more about SEQUENCE function in Excel in this article on our blog.

Hi there,

You provided the following formula =TRUNC(MOD((ROW(A1)-1)/20,3000))+$D$1

$D$1 - your date.

I've used, but it only adds one day to the original date ($D$1) to every block of 20 rows. Can we get the same result but have it add 7 days to each block of 20 rows?

ie:

Jan 10, 2021

... (repeat n rows)

Jan 10, 2021

Jan 17, 2021

... (repeat n rows)

Jan 17, 2021

Jan 24, 2021

... (repeat n rows)

Jan 24, 2021

Jan 31, 2021

... (repeat n rows)

Jan 31,2021.....repeat for 1yr, 2yrs, 3yrs...etc...

Cheers!

Hello!

If I understand your task correctly, the following formula should work for you:

=CEILING(SEQUENCE(1000,1,1,1)/20,1)*7+$D$1

You can learn more about CEILING function in Excel in this article on our blog

Hi there. Is there any way to maintain text data entered into a row underneath a given date that won't show on that same cell every month?

For example, the calendar has been set up with array rows 4, 6, 8, 10, 12 & 14. Therefore there are rows that I wish to type text inserted as rows 5, 7, 9, 11, 13 & 15.

I select Monday, 5th of July 2021 (row 6). I type in the text underneath - in row 7 (column C).

I change the month to August. Dates change of course, however, the text remains in row 7, column C.

Is there any formula I can use to reset the cell given a change of month?

Thanks

Hello!

If you want not to display text when the date changes, then use the IF function and this guide. You can delete text using a VBA macro.

I hope I answered your question. If something is still unclear, please feel free to ask.

Good Morning,

I am trying to format the dates in cells for a spreadsheet that spits out of one of our reporting systems and I can't seem to get it formatted. For example, all of the dates are formatted as such: "2021-01-04T00:00:00:000"

Is there a quick way to format them to all say "MM/DD/YYYY"? I tried doing it through the Format Cells options and it won't allow me to do so. Thanks!

Hello!

I think your date is written as text. Try this formula:

=--LEFT(A1,10)

Then apply the date format you want to the cell.

I hope it’ll be helpful.

Hello!

I am trying to make an IF statement for frequency of dates from a specified start date. I have gotten the weekly and fortnightly with the start date cell (such as B11) +7 or +14 correspondingly, but I am not sure how to do the month formula. I just would need the same day each month, same as when we autofill dates, but in a formula.

Hello!

Your task is not completely clear to me. Without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result.

Hello. I am using a calendar template but was wondering if there was a way to use information from one spreadsheet to auto-populate my calendar. I am trying to use the calendar to show when individuals on my team are available. Example

Member 1 Available on July 23, 2021.

Member 2 Available on July 23, 2021

Member 3 Available on August 5, 2021

Is there a way to do this?

Thank you