by 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:
- 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.
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:
- 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:
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:
- 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.
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:
- 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.
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:
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.
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:
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:
To generate a date sequence that increments by one month and starts with a specific date, use the SEQUENCE function together with EDATE:
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))
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:
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:
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:
If you do not want to bother calculating the step manually, you can define it by using the TIME function:
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))
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)
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.
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)
291 comments
what i do for get sequence of two numbers
exp.
1-2
3-4
5-6
7-8
Hi!
Please re-check the article above since it covers your case. Use the & operator to concatenate values.
=SEQUENCE(50,1,1,2)&"-"&SEQUENCE(50,1,2,2)
Hello! I was wondering if you would be able to help me with a date sequence question. I have also read your sequence post and was not able to find the answer.
I am trying to use a filter function (FILTER(B:B,(B:B>=$L$2)*(B:B<=$M$2),"No data")) that tells me dates I have within a set of data. The problem I am having is that I did not write every date (there are too many) so I wrote the start and end date like the following:
C D
Saturday, December 1, 2018 Friday, March 1, 2019
Monday, March 4, 2019 Friday, March 8, 2019
Monday, March 11, 2019 Friday, March 15, 2019
Monday, March 18, 2019 Friday, March 22, 2019
Monday, March 25, 2019 Tuesday, March 26, 2019
Sunday, April 14, 2019 Thursday, May 30, 2019
Saturday, June 1, 2019 Tuesday, March 31, 2020
(This is in date format, not text)
I wanted it to recognize that these are ranges so that I can execute the FILTER function as planned.
I tried creating a sequence function (C2+SEQUENCE(1+D2-C2,,0)) but cannot drag it for the rest of the rows because of a spill error.
I was wondering if you would be able to provide me with a sequence function I can use that will allow me to infinitely sequence the dates within the range in a single column for each row without the spill error, or if you can help me figure out another way to do a filter function for dates when you only have the the start/end date of ranges.
Thank you!
Hello!
It's not clear from your description what conditions you want to use in FILTER function. What is written in L2 and M2? An example of your data is the C and D columns, which are not in the formula. Your question is not entirely clear, please specify.
I am creating a workbook and my raw data tab has numerous groups names that I need to assign the same DOW. I then want the same number of cells for the next DOW. How can I auto generate this without having to copy and paste.
I.E.
Sunday
Sunday
Sunday
Sunday
Monday
Monday
Monday
Monday
Tuesday
Tuesday
Tuesday
Tuesday
Hello!
Use the SEQUENCE function to create a sequence of dates. Use the TEXT function to convert a date to a day of the week.
=TEXT(WEEKDAY(CEILING(SEQUENCE(60,1,1,1)/4,1),2)+1,"ddddd")
I hope this will help.
Good day
i need to auto fill date but the are not all in a straight line, each one is in its own cell and the a a few cells apart from each other.
thankyou
paulo
Hi!
The information you provided is not enough to understand your case and give you any advice. You might find this article helpful: How to use AutoFill in Excel - all fill handle options.
hello!!
Is there a way to expand cascading data in full detail, please see below example:
FROM:
Date Term Amount
Jan-22 1 $20
Feb-22 6 $26
Aug-22 3 $30
TO:
Date Amount
Jan-22 $20 1 amount for Jan as per term above
Feb-22 $26 from Feb $26 repeats 6 times as per term above
Mar-22 $26
Apr-22 $26
May-22 $26
Jun-22 $26
Jul-22 $26
Aug-22 $30 from Aug $30 repeats 3 times as per term above
Sep-22 $30
Oct-22 $30
I want to make function to make numerical series in column a:a based on entered values in column b:b
the condition is when the values repeated in b:b the the series in a:a takes the same value else complete series
ex
a b
1 20
2 41
3 45
3 45
4 64
5 69
5 69
6 75
7 77
Hi!
If the values in column B are sorted in ascending order, then in A1 write the number 1, and in A2 write the formula
=--(B2<>B1)+A1
After that you can copy this formula down along the column.
Trying to autopolulate dates that are two weeks apart. Please help!
This is examples of what I would like it to look like.
01-02-22 to 01-15-22
01-16-22 to 01-29-22
01-30-22 to 02-12-22
02-13-22 to 02-26-22
02-27-22 to 03-12-22
03-13-22 to 03-26-22
03-27-22 to 04-09-22
04-10-22 to 04-23-22
04-24-22 to 05-07-22
05-08-22 to 05-21-22
05-22-22 to 06-04-22
Hello!
The values you want to get are text. You can create a sequence of dates in two columns using the formula
=A1+14 and =B1+14
Then concatenate the dates in desired format.
For example
=CONCATENATE(TEXT(A1, "mm-dd-yy")," to ",TEXT(B1, "mm-dd-yy") )
i need to insert value USED start date TO end date used pick date in excel
exempt the value pick date 01/01/22 to 04/01/22
01/01/22 I USED
______________________
02/01/22 I USED
_____________________
03/01/22 I USED
______________________
04/01/22 I USED
______________________
05/01/22 I
______________________
06/01/22 I
.
.
.
.
Hi!
If I understand your question correctly, then you need to use the IF function with dates:
=IF(AND(A1>DATE(2022,1,1),A1<DATE(2022,5,1)),"USED","")
You can also learn how to use IF AND.
Exciting new feature and thank you for the breakdown. Do you know if there is a way for excel to acknowledge historical dates? My Date column will format appropriately for 1900+ dates but pre-1900 dates don't convert past YYYY-MM-DD. Am I doing something wrong? Or has Microsoft not yet integrated this feature for us history buffs?
Hello!
Unfortunately Excel does not recognize dates before 1900. In the internal Excel system dates are stored as serial numbers beginning with January 1, 1900, which is stored as 1.
According to Microsoft you can use a VBA macro to work with dates before 1900.
Read this guide:
https://support.microsoft.com/en-us/help/245104/how-to-calculate-ages-before-1-1-1900-in-excel
Hello - great article! Just wish my brain could grasp some things. Thanks so much time for posting - sincerely appreciate.
I need the following. Hopefully it can be done.
6 of same date for a Monday
4 of same date for a Tuesday
6 of same date for a Wednesday
4 of same date for a Thursday
6 of same date for a Friday
6 of same date for a Saturday
0 (skip Sunday)
Repeating through the end of the year.
It can start on January 1, 2021, which is a Friday, if that makes it easier. Or, can add a Sunday if that's easier. I wouldn't need it to say the day next to the example below, the day is just provided for example of repeating pattern.
Below would be example:
1/4/2021 Monday
1/4/2021 Monday
1/4/2021 Monday
1/4/2021 Monday
1/4/2021 Monday
1/4/2021 Monday
1/5/2021 Tuesday
1/5/2021 Tuesday
1/5/2021 Tuesday
1/5/2021 Tuesday
1/6/2021 Wednesday
1/6/2021 Wednesday
1/6/2021 Wednesday
1/6/2021 Wednesday
1/6/2021 Wednesday
1/6/2021 Wednesday
1/7/2021 Thursday
1/7/2021 Thursday
1/7/2021 Thursday
1/7/2021 Thursday
1/8/2021 Friday
1/8/2021 Friday
1/8/2021 Friday
1/8/2021 Friday
1/8/2021 Friday
1/8/2021 Friday
1/9/2021 Saturday
1/9/2021 Saturday
1/9/2021 Saturday
1/9/2021 Saturday
1/9/2021 Saturday
1/9/2021 Saturday
Thanks.
Hi!
Regrettably, we don’t know the way how to do this.
Hello, I need to auto fill Column A with a pay period date range and type in a formula to get the next date range so I don't have to manually type the next pay period for the next year. Some dates have already been entered above so I am on line 190. I typed my date range 3/6/22-3/12/22 and went down to A191 and typed in A191+7 like you suggested above but I get a value error after hitting enter. Can you tell me how to achieve this? I want it to look like this below without manually entering the pay periods for the next year.
Column A
3/6/22-3/12/22
3/13/22-3/19/22
3/20/22-3/26/22
Hello!
Your date range is text. You can get it by merging cells. In these cells, you can create a sequence of dates =A1+7 and =B1+7
I hope it’ll be helpful. If something is still unclear, please feel free to ask.
HI
I want to creat a spreadsheet that will increment the dates i.e. 24/2/2022 to 2/3/2022, but the following week I only want to change the first date and the spreadsheet automatically change the following dates.
Im struggling to make this happen at present
any help appreciated
Thank you
Hello!
To create a date sequence, write the date in the first cell and add 1 to that date in the next cell
=A1+1
After that you can copy this formula down along the column.
Hi,
I'm trying to create a list of due dates based on a target end date. So I want to calculate 4 weeks before a date, 3 weeks before, 5 days before, etc. I can find gantt charts that create based on a start date, but nothing based on an end date.
Thank you.
Hello!
You may find this article useful: Subtract dates in Excel; add days, weeks, months or years to date. If this is not what you wanted, please describe the problem in more detail.
Hi,
Thank you for the tips. I would like to create a sequence of 3 working days per week, say Tuesday, Thursday and Friday. How do I do that?
Many thanks
Hi!
There is no logical sequence in these 3 days.
I need help with date sequence. I have 4 units I make a day. 20 units each week. Skipping wknds. I would like to know as my list grows of units how far the date will be.
1 2-7-22
2 2-7-22
3 2-7-22
4 2-7-22
5 2-8-22
6 2-8-22
7 2-8-22
8 2-8-22
And so on and so on. Is there a way of doing this?
Hello!
Please try the following formulas:
=SEQUENCE(100,1,1,1)
=DATE(2022,2,6)+CEILING(SEQUENCE(100,1,1,1)/4,1)
You can learn more about SEQUENCE function in Excel in this article on our blog
Hi! I am trying to create a formula on a workstream tracker where the end calendar date for a task will automatically populate based on the start date entered and another column showing the number of workdays a task takes. Can a formula be customized to do that? Thanks for any insight!
Hello!
I recommend reading this guide: Subtract dates in Excel; add days, weeks, months or years to date.
If this is not what you wanted, please describe the problem in more detail.
Hello I work 3 days one week and 4 days the next, I would like to create a performance log for my own benefit that will show the dates I work going forward for the entire year. I would like to have it auto populate so I don't have to type the date all the time. I work 12hrs Sun-Tues, then Sun-Wed and I cannot seem to figure out how to do this.
Hi!
I don't think your problem can be solved with the Excel sequence formula.
Hi
I have made EXCEL payroll but want to populate dates automatically every year for 26 pay periods, keeping in mind that some months there are only 2 pay periods and 2 of the 12 months will have 3 pay periods.
I wish to populate the payroll dates for each month in a separate row, if there is no 3rd pay period in that month and then it remains blank, for example in the EXCEL file below, April and October has 3 payroll periods and remaining have 2.
If there a simple formula to populate these dates automatically for the year in their respective cells.
I used the following formula
=IF(MONTH(C3+14)=A3,SUM(C3, 14),"")=IF(MONTH(C3+14)=A3,SUM(C3, 14),"")
But still need to update manually when 3rd pay period is blank.
Biweekly Payroll (Dates setup)
Month Pay 1 Pay2 Pay3
1 8-Jan-22 22-Jan-22
2 5-Feb-22 19-Feb-22
3 5-Mar-22 19-Mar-22
4 2-Apr-22 16-Apr-22 30-Apr-22
5 14-May-22 28-May-22
6 11-Jun-22 25-Jun-22
7 9-Jul-22 23-Jul-22
8 6-Aug-22 20-Aug-22
9 3-Sep-22 17-Sep-22
10 1-Oct-22 15-Oct-22 29-Oct-22
11 12-Nov-22 26-Nov-22
12 10-Dec-22 24-Dec-22
Would appreciate if there is simple formula to automatically calculate the dates, and if the 3rd dates doesn't fall in the same month then should go to next row, with appropriate month number.
Thanks
Hello!
Cell A3 - date 8-Jan-22
Cell B3 =A3+14
Cell C3 =IF(MONTH(B3+14)=MONTH(B3),B3+14,"")
Cell A4 =IF(C3="",B3+14,C3+14)
Copy the formulas down the column.
This should solve your task.
Thanks
Hello. I need to create a spreadsheet that has 53 weeks. Each "row" is a week and there are 5 columns that represent the weekdays. I want to be able to enter the first date of our "calendar" into the first cell of upper left row/column and all workdays are automatically filled in. If this isn't possible, I'd settle for simply entering in the "Monday" date and it automatically filling in the next four columns with the next four weekdays to follow. I know I can use the autofill feature, but I will have to redo this calendar every year and would ideally love to be able to simply enter in a single date in the first cell OR only 52 dates in each row straight down the first column and all other information automatically adjusting. Is this possible?
2/7/2022 | 2/8/2022 | 2/9/2022 | 2/10/2022 | 2/11/2022
2/14/2022 | 2/15/2022 | 2/16/2022 | 2/17/2022 | 2/18/2022
etc...
Hello!
In cell A1, enter the date for Monday. In cell B1, enter the formula =A1+1. Copy this formula row by row up to column E. In cell A2, write =A1+7 and copy to the right row by row. Highlight range A2: E2 and copy down.
Thank you Alexander.
I would like to use a fill function on dates. The sequence should be based on days of the week and I want excel to sequentially fill in the dates based on one week of completed info.
So, for example:
Tuesday, 11 January, 2022
Wednesday, 12 January, 2022
Wednesday, 12 January, 2022
Thursday, 13 January, 2022
Friday, 14 January, 2022
Saturday, 15 January, 2022
Saturday, 15 January, 2022
Monday, 17 January, 2022
I want it start on Tuesday, which would be 18 January 2022 and fill in the days while being aware of the days that appear twice like Wednesday and Saturday.
Hello!
Your dates don't have any logical pattern. Sorry, I cannot help you.