How to create a sequence of dates in Excel and auto fill date series

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.

How to fill date series in Excel

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

Auto fill a date series in Excel

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

  1. Type the initial date in the first cell.
  2. 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.
Auto fill a date series in Excel.

Fill a column with weekdays, months or years

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 Optionsbutton and pick the desired option, say Fill Months:
    Auto fill a series of months in Excel.
  • 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:
    Auto fill a series of years in Excel.

Fill a series of dates incrementing by N days

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

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

Fill a series of dates with a specific step.

For more examples, please see How to insert and autofill dates in Excel.

How to make a date sequence in Excel with a formula

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.

Note. All the formulas discussed here only work in the latest versions of Excel 365 that support dynamic arrays. In pre-dynamic Excel 2019, Excel 2016 and Excel 2013, please use the AutoFill feature as shown in the first part of this tutorial.

Create a series of dates in Excel

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.
Creating a sequence of dates in Excel with a formula

Note. With the default General format, the results will appear as serial numbers. To have them displayed correctly, be sure to apply the Date format to all the cells in the spill range.

Make a series of workdays in Excel

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.
Making a series of workdays in Excel with a formula

Tips and notes:

  • 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.

Generate a month sequence in Excel

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 1st argument and day in the 3rd argument. For the 2nd 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:
Generating a sequence of months 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))
Making a month series based on a specific start date

Note. After completing a formula, please remember to apply an appropriate date format to the results for them to display correctly.

Create a year sequence in Excel

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:
A formula to output a year sequence in Excel

Generate a times sequence in Excel

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:
Generating a sequence of times in Excel

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))
Formula to create a time sequence in Excel with any increment step size

How to create a monthly calendar in Excel

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)
Formula to create a monthly calendar in Excel

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 1st 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 1st 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 1st 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 1st 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.
Set up a conditional formatting rule to hide irrelevant dates.

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!

Practice workbook for download

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

Table of contents

291 comments

  1. I am needing help with a formula that would allow me to have the same date in 'x' amount of rows (for this example lets say 5 rows) and then it changes to the next day for the next 5 rows and so on and so forth. With that being said, I want to only include weekdays (Monday - Friday) as well as any company holidays I may have (Example: July 4th).

    Example:
    6/30/2023
    6/30/2023
    6/30/2023
    6/30/2023
    6/30/2023
    *Skips Weekend*
    7/3/2023
    7/3/2023
    7/3/2023
    7/3/2023
    7/3/2023
    *Skips 7/4 Holiday*
    7/4/2023
    7/4/2023
    7/4/2023
    7/4/2023
    7/4/2023

    Continued until 11/1/2023. And if I needed to include additional company holidays besides the one I provided, how would I include that in the formula?

    Appreciate the help!

  2. How to create excel table to auto fill the dates in one column with the month and Year given in a cell as input and how to high light the weekend days

    Example

    Month and Year 03/2023

    Dates
    01
    02
    03
    04
    05
    06
    07

      • I want to know is it possible to autofill date in a column of particular month, when the month is provided by the user.

        • Hi!
          All the necessary information is in the article above.
          The formula below will do the trick for you:

          =DATE(YEAR(TODAY()),M1,SEQUENCE(DAY(EOMONTH(DATE(2023,M1,1),0))))

          The DAY and EOMONTH functions help you get the number of days in a month.

  3. Hi! I'm hoping to get some help if possible- I'm trying to create a daily attendance log with the date at the top right corner(dddd, mmmm dd. yyyy) that auto-updates to the next workday on print?? Is this even possible? Specifically: cell is currently set to Monday, April 03, 2023, and on Print it would auto-update to Tuesday, April 04, 2023, and so on for the month.

    Maybe I'm looking for a function that doesn't exist, but it'd sure be nice to be able to go CTRL+P at 30 "copies" and have them all automatically be from Monday-Friday for the month, instead of having to type the date in for each day.

  4. How can i have a fixed date in a cell change every 28 days within that same cell.

    Example Cell A1 '12/02/23' change to '12/03/23' on that date. Then again change to '09/04/23' on that date, and so on.

    Thanks in advance

  5. Hi, I am trying to get a formula where i can write the date in one cell then have cells three rows below auto populate the week entirely.

  6. I am trying for Date Sequence With regular interval of blank five columns but Not Working Any Suggestions ?

      • like for example i am trying for this.....
        March 2, 2023

        March 3,2023

        March 4,2023

        in this way i am trying to do if any thing possible ?

        • Hi!
          If I understand correctly, write the date in the first cell. Select that cell and the empty cell below it. Hover the mouse cursor over a small square at the lower right-hand corner of the selected range. Hold and drag the fill handle down the column.

  7. Hello,

    can anyone help me with a formula for the following:

    Tuesday, February 28, 2023 AM
    Tuesday, February 28, 2023 PM
    Wednesday, March 01, 2023 AM
    Wednesday, March 01, 2023 PM
    Thursday, March 02, 2023 AM
    Thursday, March 02, 2023 PM
    Friday, March 03, 2023 AM
    Friday, March 03, 2023 PM
    Saturday, March 04, 2023 AM
    Saturday, March 04, 2023 PM
    Sunday, March 05, 2023 AM
    Sunday, March 05, 2023 PM
    Monday, March 06, 2023 AM
    Monday, March 06, 2023 PM

    and etc....

    Thank you in advance

    • Hello!
      Write in the first cell the starting date and time, for example, 1.03.2023 01:00:00 In the next cell add to this value 0.5: A1+0.5 Copy down the column. Use a custom date format
      dddd, mmmm dd, yyyy AM/PM
      I hope my advice will help you solve your task.

  8. I am trying to produce a spreadsheet with a month per sheet for the financial year, each sheet having headers for week commencing with Monday dates. I want to be able to enter the first Monday of the financial year and all the dates automatically enter into the headers. Initial this seemed simple enough with a cell+7 formula. However, as I want to be able to use this same spreadsheet template year after year, this does not allow for the fact that some years a month may have four Mondays, but five Monday a subsequent year. I worked out a formula to only enter a date in the final column if it was before the 1st of the following month, however this only seems to work if there is a year in the date, meaning I would have to change the formulas each financial year (which I don't want to have to do, just change the date of the first Monday). Is there a way to do this?

  9. Till now, the autofill of sequential dates has worked, but suddenly it doesn't anymore. Is there a setting or something that blocks the autofill feature?

    • Yes I have the same issue since a couple of weeks, haven been searching everywhere. It's NOT the options as you read everywhere... but no autofill anymore for numbers and dates, I presume since one of the recent updates ?

  10. I want to fill a row with the days of week. Cell 1 1/1/2023-1/7/2023 then rack cell on row after that the next calendar week... 1/8/2023-1/14/2023,1/15/2023-1/21/2023, etc

    • Hi!
      Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

    • Michelle,

      I'm looking to do something similar but couldn't accomplish using the options described here. Were you successful? Thanks!

  11. I would like to know the steps to create Date sequence horizontally excluding the weekends ( Saturday and Sunday )

  12. I was curious if someone knew how to make a date sequence formula skipping five rows and continuing the sequence? I am trying to make a calendar with four cells for each day to input data.

  13. Is there a way to type in a start date (example: 1/2/23) and an end date (example: 1/24/23) and have excel automatically fill in the dates between the start/end in rows below? This is for a time sheet with variable time periods, where I'd like the employees to be able to enter in the start/end dates of the time period, but not have to write in each date, or have to click and drag down the column using the "B2+1" formula. Basically is it possible for excel to expand dates in "1/2/23" format, from a start and end date, that is a variable length of time (not each time period is the same number of days long). Thanks so much!

    • Hi!
      All the necessary information is in the article above.
      D1- start date. D2 - end date. Try this formula:

      =SEQUENCE(D2-D1+1,1,D1,1)

  14. Hello,

    Looking to create a table where the same date is listed for that week and then adds 7 days for the next set - I.E

    12/14/2022
    12/14/2022
    12/14/2022
    12/14/2022
    12/14/2022
    12/21/2022
    12/21/2022
    12/21/2022
    12/21/2022
    12/21/2022
    12/28/2022
    12/28/2022
    12/28/2022
    12/28/2022
    12/28/2022

      • Hi,
        Looking to create a table where the same date is listed 3 times for one day, then sequence for 1 year

        01/01/2024
        01/01/2024
        01/01/2024
        02/02/2024
        02/02/2024
        02/02/2024

  15. Hi Guys,

    Would anyone know how to autofill date on every let's say 50th row? So if I have 19/12/2022 in A1 and want to autofill 20/12/2022 in A50. Is there an easy way of doing this? I know you can drag the date down with a mouse and select a row on which you want to autofill the next day but I would have to do it 365 times.

    Any feedback much appreciated.

    Thank you.

    • Hello!
      Create a start date using the DATE function and add a sequence of numbers. If I understand your task correctly, try the following formula:

      =DATE(2022,12,19) + CEILING(SEQUENCE(365,1,1,1)/49,1)-1

  16. So I have a set of dates for Mondays.

    Is it possible to change / update the first cell sequentially like every 4 weeks?

    For example:

    Mon, 21 Nov 22
    Mon, 28 Nov 22 (+7)
    Mon, 5 Dec 22 (+14)
    Mon, 12 Dec 22 (+21)

    I'm trying to automatically change Mon, 21 Nov 22 every 28 days

      • Sorry, I guess I wasn't clear. To give context, I'm using =Today()-Weekday(Today())+2 to determine the Monday for the current week.

        So for this week it is Mon, 21 Nov 22

        I'm trying to auto update this cell because a cycle takes 4 weeks or 28 days to renew. If I leave it as is, the date will update weekly and that will mess with the assignments for the other set dates since the cycle hasn't been completed.

        Assume A,B,C,D are people. Each week there are tasks assigned only for column 1 (they mentor a specific team member in column B).

        A,B,C - D - Mon, 21 Nov 22
        A,B,D - C - Mon, 28 Nov 22
        A,C,D - B - Mon, 5 Dec 22
        B,C,D - A - Mon, 12 Dec 22

        I hope this makes sense, if its not possible it's fine. Was worth asking / trying. Thanks

  17. I need to create a sequence for attendance period from 26th of current month to 25th of subsequent month. I am mentioning from date in cell "A2", then providing equation in cell "B5" as "=A2" and setting custom format to "DD". Cell "C5" to "AF5" equation is set "+1". Let's say A2 is 26/10/2022, hence B5 is 26 and AF5 is 25. if I change date in A2 to 26/11/2022, AF5 will be 26 in this case. Since it crosses attendance period of 25th, AF5 should become blank. Please guide what excel formula will help this scenario.

  18. Hello
    I would like to know how I can create a formula with a sequence of 4 days in and 4 days out, including weekends. Thank you

    • Hi!
      If I got you right, the formula below will help you with your task:

      =WORKDAY(A1-1, TRUNC(MOD((SEQUENCE(100,1,1,1)-1)/1,4)+1) + (CEILING(SEQUENCE(100,1,1,1)/2,2)-2)*4)

  19. Hello!

    If you can help me, please. I have to generate for every month a sheet with 19-25 (25 week days and 19 in weekends) intervals per every day, from half to half a hour, start from 8:00 AM and end at 8:00 PM in week days and in weekend start from 9:00 AM and end at 6:00 PM.

    EX: 10/9/22 is Friday and 10/10/2022 is Saturday
    10/9/2022 12:00 PM
    10/9/2022 12:30 PM
    10/9/2022 1:00 PM
    10/9/2022 1:30 PM
    10/9/2022 2:00 PM
    10/9/2022 2:30 PM
    10/9/2022 3:00 PM
    10/9/2022 3:30 PM
    10/9/2022 4:00 PM
    10/9/2022 4:30 PM
    10/9/2022 5:00 PM
    10/9/2022 5:30 PM
    10/9/2022 6:00 PM
    10/9/2022 6:30 PM
    10/9/2022 7:00 PM
    10/9/2022 7:30 PM
    10/9/2022 8:00 PM
    10/10/2022 9:00 AM
    10/10/2022 9:30 AM
    10/10/2022 10:00 AM
    10/10/2022 10:30 AM
    10/10/2022 11:00 AM
    10/10/2022 11:30 AM
    10/10/2022 12:00 PM
    10/10/2022 12:30 PM
    10/10/2022 1:00 PM
    10/10/2022 1:30 PM
    10/10/2022 2:00 PM
    10/10/2022 2:30 PM
    10/10/2022 3:00 PM
    10/10/2022 3:30 PM
    10/10/2022 4:00 PM
    10/10/2022 4:30 PM
    10/10/2022 5:00 PM
    10/10/2022 5:30 PM
    10/10/2022 6:00 PM

    I hope it's posibile :(
    Thank you verry much!

    • Hi! :)

      I've tyried something, that works, but not completly well. I cant SEQUENCE the SEQUENCE :)) (if it's posible). If i modify the start date all is good, but i need to populate the sheet with all days :(, not one by one.

      My formula " =IF(WEEKDAY(R1,2)<6,SEQUENCE(25,1,DATE(YEAR(R1), MONTH(R1), DAY(R1)),0)+SEQUENCE(25,1,TIME(8,0,0),1/48),SEQUENCE(19,1,DATE(YEAR(R1), MONTH(R1), DAY(R1)),0)+SEQUENCE(19,1,TIME(9,0,0),1/48)) "

      in R1 is the date 10/1/2022

      Thank you.

        • Hey!

          Sorry for that :))..
          Short story. On A colomn I need to generate for every weekday a set of 25 interval per day (until the end of the world :)) ).

          Like example, for day 3 Aug 2022 (weekday,25 intervals 8AM-8PM):
          10/3/2022 8:00 AM
          10/3/2022 8:30 AM
          10/3/2022 9:00 AM
          10/3/2022 9:30 AM
          10/3/2022 10:00 AM
          10/3/2022 10:30 AM
          10/3/2022 11:00 AM
          10/3/2022 11:30 AM
          10/3/2022 12:00 PM
          10/3/2022 12:30 PM
          10/3/2022 1:00 PM
          10/3/2022 1:30 PM
          10/3/2022 2:00 PM
          10/3/2022 2:30 PM
          10/3/2022 3:00 PM
          10/3/2022 3:30 PM
          10/3/2022 4:00 PM
          10/3/2022 4:30 PM
          10/3/2022 5:00 PM
          10/3/2022 5:30 PM
          10/3/2022 6:00 PM
          10/3/2022 6:30 PM
          10/3/2022 7:00 PM
          10/3/2022 7:30 PM
          10/3/2022 8:00 PM
          10/4/2022 8:00 AM
          10/4/2022 8:30 AM
          10/4/2022 9:00 AM
          10/4/2022 9:30 AM
          10/4/2022 10:00 AM
          10/4/2022 10:30 AM
          ...same pattern for 4,5,6,7,8 Aug...
          ....then at 9 and 10 Aug when is weekend i need to generate only 19 intervals (from 9AM to 6PM)
          10/9/2022 9:00 AM
          10/9/2022 9:30 AM
          10/9/2022 10:00 AM
          10/9/2022 10:30 AM
          10/9/2022 11:00 AM
          ....
          10/9/2022 4:30 PM
          10/9/2022 5:00 PM
          10/9/2022 5:30 PM
          10/9/2022 6:00 PM
          ...then for 10 Aug...
          ..then 11 Aug...
          untill the end of time :D

          Thank you.

          • If you can, in a new excel put this on A1: =IF(WEEKDAY(R1,2)<6,SEQUENCE(25,1,DATE(YEAR(R1), MONTH(R1), DAY(R1)),0)+SEQUENCE(25,1,TIME(8,0,0),1/48),SEQUENCE(19,1,DATE(YEAR(R1), MONTH(R1), DAY(R1)),0)+SEQUENCE(19,1,TIME(9,0,0),1/48))

            And the date in R1 (format m/dd/yyyy)

            This will populate A1 to A25 or A19(depend if is weekday or weekend), but i need this to the down end of the colomn A, for each day :D. Practicaly i need to sequence the sequence :))....

            Sorry for bother.

  20. Hi, i wanna ask if a database as > | 28-Apr-22 | 29-Apr22| 2 | = |[from date]|[to date]|[number of day]|, can we seperate the date into 2 database in excel?

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)