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

268 comments

  1. Hello, I am wanting to put weekly dates for one year down "A" column.

    DATE
    1/1/24 - 1/7/24
    1/8/24 - 1/14/24
    1/15/24 - 1/21/24
    1/22/24 - 1/28/24
    1/29/24 - 2/4/24

    • Hi! Use the instructions from the article above and create two date sequences. To set the start date, you can write the date as a number or use the DATE function. Convert these dates to text in the desired format using the TEXT function. Combine these dates into a single text string using the CONCATENATE function.
      Here are sample formulas for your question:

      =CONCATENATE(TEXT(SEQUENCE(50,1,45292,7), "mm/dd/yy")," - ",TEXT(SEQUENCE(50,1,45298,7), "mm/dd/yy") )
      =CONCATENATE(TEXT(SEQUENCE(50,1,DATE(2024,1,1),7), "mm/dd/yy")," - ",TEXT(SEQUENCE(50,1,DATE(2024,1,7),7), "mm/dd/yy") )

  2. Hi, I am trying to find a suitable formula for the following and it's driving me mad. I need a spreadsheet that has 2 rows per day, to looks like:

    01/01/2024
    01/01/2024
    02/01/2024
    02/01/2024
    03/01/2024
    03/01/2024
    04/01/2024
    04/01/2024

    Could anyone help me with this please? Thankyou so much!

    • Hi! There are also such examples in the comments above. Use this formula and apply the date format you need.

      =45291+CEILING(SEQUENCE(100,1,1,1)/2,1)

      45291 - that's 31/12/2023 converted into numbers.

  3. In excel one column has date 01-01-2022 and another column has date 21-12-2023 so i want total number of months between these two dates date wise for each month from 01-01-2022 to 31-01- 2022 Total Days 31 to 21-12-2023 Cumulative dates one below the other as 01-01-2022 to 31-01-2022 Total Days 31, 01-02-2022 to 28-02-2022 Total Days 28, as If you want to bring one below, give the formula how to bring it

  4. Hi Need to create the batch number using formal. Please help

    Item WH DOMDate Batch Number
    S00903 Delhi 02-07-2023 2
    S00903 Delhi 01-07-2023 1
    S00903 Chennai 13-08-2023 1
    S00903 Chennai 27-10-2023 2
    S00903 Bangalore 01-Sep-23 1
    S00903 Bangalore 20-Sep-23 2
    S00903 Bangalore 01-Oct-23 3
    S00903 Bangalore 01-Oct-23 3
    S00903 Bangalore 01-Oct-23 3
    S00904 Delhi 02-08-2023 2
    S00904 Delhi 01-06-2023 1
    S00904 Chennai 13-08-2023 1
    S00904 Chennai 27-10-2023 2
    S00904 Bangalore 01-Sep-23 1
    S00904 Bangalore 20-Sep-23 2
    S00904 Bangalore 01-Oct-23 3
    S00904 Bangalore 01-Oct-23 3
    S00904 Bangalore 01-Oct-23 3

  5. Hi, im doing project planning using excel. the project has a date and time for each task and if it exceeded 5pm, it should set the finish date to the next day.

    My question, is there a way for me to set a time limit for that specific date and if the time exceeds, it would automatically change to the next date.

    for example:-

    start date= 22/10/23. start time= 08:00am. finish time= 07:30am . time limit is from 8am until 5pm. since the finish time is 7:30pm, it should finish on date 23/10/23. How do i automatically change the date?

    i am having a hard time translating time into date. Thank you in advance

    • Hi! Unfortunately, your description is not enough to give you accurate advice. If your end date and end time are written in separate cells, you can use the IF formula.
      You can calculate the new end time using this formula:

      =IF(C1>TIME(17,0,0),TIME(8,0,0)+(C1- TIME(17,0,0)),C1)

      The formula for the end date:

      =IF(C1>TIME(17,0,0),A1+1,A1)

      A1 - start date
      C1 - end time
      I hope it’ll be helpful. If this is not what you wanted, please describe the problem in more detail.

  6. hello id like help to make this,

    12/13 8 12/14 8 ect..

    this goes on for two weeks not including weekends how do i format that please!

  7. Hello,

    I am wanting do a formula that inputs every Sunday of the month without having to imput manually?

    Can anyone help?

    example:

    22-Oct 29-Oct 05-Nov 12-Nov 19-Nov 26-Nov

    • Hi! Read carefully the guidelines and examples in the article above. Here is an example formula for a sequence of dates with an interval of 7 days:

      =SEQUENCE(6,1,DATE(2023,10,22),7)

    • When it hit 3/24/24 it goes all haywire from there and then It shows this
      45382
      45389
      45396
      45403
      45410
      45417
      After putting in the above formula. How do I fix this?

      The first 13 are correct.

  8. Hello, I'd like help with a formula where I can input a date, and it will give me four columns, one 82 days and 24 days from that date, and the other 82 days and 24 days beforehand.
    So if I entered January 1st, 2023, one column would be 82 days from 1/1/23, second column 24 days from 1/1/23, third column would be 82 days before 1/1/23, and fourth column 24 days before 1/1/23.
    Thanks so much!

  9. I asked about creating a medicine refill calendar where I could put the date I filled and then next cell has # of days until refill again. I have several meds and each has a different number of days between refills, so it is VERY confusing to keep track or order a couple at the same time instead of going to the pharmacy every week multiple times. I was given the following formula, but it only works for the current year I found out today. How can I get this formula to go into the next year. SEQUENCE(,INT((DATE(YEAR(B2),12,31)-B2)/C2),$B2+C2,$C2) I can't attach a copy of excel spreadsheet, so I'll try to explain my excel sheet looks like. A1 is Labeled Prescription, a2 is name of med, a3 is next name of med. Then B1 is Labeled Start date, b2 next med start date and so on down the column. C1 is number of days until I can refill. This changes on each cell in column C. Then finally column D2 and so on down, has each refill date calculated from refill + number of days that I can call in a refill. I can spread this across column E<F< G and so on. I spread this across for several months but see that I can't go into 2024 as I now have an error #CALC! Thank you so much for any input I can have to help figure this out.

  10. Hi,

    I need make a list of days and every hour of every day.

    ie.

    1/1/2023 00:00
    1/1/2023 01:00
    1/1/2023 02:00

    for all 365 days

  11. Hi! Can someone help me?

    I have a start date and finish date.
    I want to count the number of days from start to finish date that is within another set of start date to today date.

    Thank you so much!

    Example:
    Days to count >>>
    Start Date: August 1
    Finish Date: August 5

    Within thus Date range
    Start Date: July 31
    Today Date: August 4

    Thank you so much!

    • Hi! If I understand correctly, you want to count the number of dates that are included in both of these time intervals. Use SUMPRODUCT function:

      =SUMPRODUCT(--(A1:A10>=MAX(B1:C1)),--(A1:A10<=MIN(B2:C2)))

      B1,B2 - first date range, C1,C2 - second date range.

  12. Hi, may I know which formula could I use to calculate total hours for overtime done by employee. I also have a problem of fixing the time in and time out format to generate the total hours automatically.
    Thank you in advance

  13. Hello, I need generate a secuence of duplicate dates using vba such as:

    15/09/2023 9:13 p. m. 20/09/2023
    16/09/2023 9:13 a. m. $15.000
    16/09/2023 9:13 p. m. $15.000
    17/09/2023 9:13 a. m. $15.000
    17/09/2023 9:13 p. m. $15.000
    18/09/2023 9:13 a. m. $15.000
    18/09/2023 9:13 p. m. $15.000
    19/09/2023 9:13 a. m. $15.000
    19/09/2023 9:13 p. m. $15.000
    20/09/2023 9:13 a. m. $15.000
    20/09/2023 9:13 p. m. $15.000

    Thanks a lot

  14. I need to make a list of 2 columns, first column starts with 9/17/23, and goes for 1 year, but each date is 6 weeks apart. I see how I can do it daily, monthly, etc, but not 6 weeks Thanks

  15. Hi there,

    Do you know how I would go about creating dates for a 4 on and 4 off shift pattern?

    For example:
    Start Date 28/07/2023

    28/07/2023
    29/07/2023
    30/07/2023
    31/07/2023
    05/08/2023
    06/08/2023
    07/08/2023
    08/08/2023
    13/08/2023
    14/08/2023
    15/08/2023
    16/08/2023

    Hope this makes sense!

    Any help would be much appreciated :)

  16. Hi

    I'm trying to create a registers using excel where the class is only seen 2 days a week
    For example I would like it to read

    Monday 04/09/2023
    Wednesday 06/09/2023
    Monday 11/09/2023
    Wednesday 13/09/2023

    etc
    Is this possible?

  17. I am trying to create a series of dates which I want to sort into a larger collection of dates.
    Surely there is a simple way to do this!

    It appears if I use the SEQUENCE formula to create the list that I am not able to sort this list.
    When I do a right click on the Fill handle of a cell that has a date from which I wish to create a date list, I am not able to select any options - they are all greyed out.
    It used to be that you could start a date list with a minimum of three dates and then use the Fill handle to extend the list into the future but now all that seems to happen when I do this is a replication of the dates in the list, not a continuation of the list.

    Any other suggestions?

  18. I'm trying to fill a sequence for the end of every quarter starting on 9-23:
    09-23
    12-23
    03-24
    06-24
    09-24
    12-24
    03-25...

  19. Abraham, Mampatta Israel
    8/6/2020
    8/13/2020
    Abram, Eulis
    4/16/2021
    5/11/2021
    Abreu, Nekita M
    4/23/2020
    5/5/2021
    Acharya, Ajay Madhusudan
    9/4/2020
    Adams, Anne K
    8/31/2020
    Adams, Elke G
    4/16/2021

    I have 10000 data in one column, and I need name in one column and in other dates mentioned underneath

    • Based on your description, it is hard to completely understand your task. If you want to transform your data into 3 columns, I don't think it is possible. Your data does not have the same pattern (two dates and one date).

  20. Hi. How can I create a sequence of dates of workdays like Monday, Wednesday, Friday or Monday to Friday. I want the formula to give me the dates of a month for Monday Wednesday Friday each week. For example for August 2023, I want the formula to give me the below dates; 2nd, 4th,7th,9th,11th,14th,16th,18th,21th,23th,25th,28th,30th.
    Is it poosibe to get that data with excel?

    • Hi! To create a sequence of working days, try using the WORKDAY.INTL function.
      Use argument "Weekend string", as described in this article.

      =WORKDAY.INTL(A1,1,"0101011")
      A1 - start date (1 Aug 2023)
      Try to enter the following formula in cell A2 and then copy it down along the column.

  21. Two dates in excel cell like 12-01-2005 & 15-02-2008
    we want in another cells as result 31-03-2005,30-06-2005,30-09-2005,31-12-2005,31-03-2006,30-06-2006,30-09-2006,31-12-2006,31-03-2007,30-06-2007,30-09-2007,31-12-2007,29-02-2008,
    How we can do with macro.

  22. I have a table with a calendar that I want to populate with a 'check mark' on specific days in a sequence. Essentially I want to take a start date for a task and a frequency for said task, and have it put a check mark on the dates at regular intervals. Is there a simple formula that would generate a sequence for me?

  23. I'm trying to repeat a sequence after every 21 rows, like first 21 rows have "7" then next 21 rows will have "8" again next 21 rows will have "9" and this will continue. how this can be achieved.

  24. I'm trying to repeat a date sequence after every 7 rows, like first 7 rows have date 6-8-23 then next 7 rows will have 7-8-23 and this will continue. how this can be achieved.

  25. Hi
    I have a financial year calendar of July 2023 to June 2024 (07/01/23 - 06/30/24) and I need to sequentially create a 52 week calendar in excel, which will be Monday to Sunday, as follows:
    07/01/23 - 07/02/23
    07/03/23 - 07/09/23
    07/10/23 - 07/16/23
    07/17/23 - 07/23/23
    07/24/23 - 07/30/23
    so on and and so forth. Can you please help formulate the same?

    Thanks in advance!

    • Hi! Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.

  26. I need to make sequence like this as given below, Please help me on this

    01 - 01/07/2023 00:00
    01 - 01/07/2023 00:10
    .
    .
    .
    01 - 01/07/2023 23:50
    01 - 02/07/2023 00:00
    02 - 01/07/2023 00:00
    02 - 01/07/2023 00:10
    .
    .
    .
    112 - 02/07/2023 00:00

  27. hi would like to insertb6 empty cells after each week.

  28. Hi,
    I have data which has been recorded every 15 minutes from between 4/1/2020-27/4/2020.
    I need to code it using R so require the layout of this spreadsheet to require 2 separate columns of date and time.
    I have added the time column but I need the date to essentially remain the same for each 24 hour period and increase by 1.
    e.g.
    4/1/2020 | 00:00:00
    4/1/2020 | 00:15:00
    4/1/2020 | 00:30:00
    ...
    ...
    ...
    4/1/2020 | 23:45:00
    5/1/2020 | 00:00:00
    5/1/2020 | 00:15:00

    and so on

    Could you help with this?
    Thanks

      • That works perfectly - thank you!

  29. I am hoping to create a excel spreadsheet that has the days of the month populate across column 5 and change monthly based on the number of days in that month I also would like to highlight every row that is a sunday hoping I can get assistance with this

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

      =EOMONTH(TODAY(),-1) + SEQUENCE(DAY(EOMONTH(TODAY(),0)),1,1,1)

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

  30. Hello, I am currently working on my study plan, and willing to share it to my friends. I would like to be able to auto change the date of each topic based on the individual's chosen number of study sessions per day.

    For example:

    In cell A1, there is an option to choose the numbers: 1 or 2 or 3 - study sessions per day.

    If I choose 1 session per day this is what the column looks like:

    column B
    July 5, 2023
    July 6, 2023
    July 7, 2023
    and so on

    For 2 sessions per day

    same column B
    July 5, 2023
    July 5, 2023
    July 6, 2023
    July 6, 2023

    Again, I would like to be able to auto sequence change the whole column based on the chosen session in cell A1.

    Thank you, I really appreciate your help! Good day!

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

      =B1+CEILING(SEQUENCE(30,1,1,1)/A1,1)

      B1 - start date

  31. I'm looking for a way, starting from 1/1/23, to have excel pull dates through the rest of the year in the following pattern: 2,3,6,7,8,11,12 (these numbers come from a 1-14 or two week interval). So for example it would return:
    2- 1/2/23
    3- 1/3/23
    6- 1/6/23
    7- 1/7/23
    8- 1/8/23
    11- 1/11/23
    12- 1/12/23
    2- 1/16/23
    3- 1/17/23
    6- 1/20/23
    7- 1/21/23
    8- 1/22/23
    11- 1/25/23
    12- 1/26/23
    2- 1/30/23
    3- 1/31/23

    • Hi! Unfortunately, I don't see any logical sequence in your numbers that can be written as a formula. If you see logic in these numbers, tell me.

  32. I've tryed to use the sequence creatio formula =WORKDAY(B2-1, SEQUENCE(B1)) and it works fine but I can't the column generenated within a vlookup array it simple doesn't read it as dates

  33. Hi. I am scouring the internet trying to find a way to store ranges of dates in excel, that automatically adjust based on the current month. So I have a list of events, and each event runs from X day to Y day. Some go into the following month. For example, Event A runs from the 12th of the month to the 08th of the following month. Is there a formula that will automatically update this for me based on today's date, in excel?

  34. if i put a date in a cell, and if the date a sunday i have to display and "S" with the date in same cell

  35. I am wanting to have the next date at the top and the last date at the bottom. Can anyone help please

    • Hi! If you want to get a descending sequence of dates from a future date to a past date, use a negative number in the [step] argument of the SEQUENCE function. For example,
      =SEQUENCE(10, 1, "8/1/2020", -1)
      Hope this is what you need.

  36. Greetings,
    My issue is probably going to be easy and I just am over looking how. I am trying to create a month long series of
    workbook pages with page 1 being the "1st" next "2nd" etc.
    Id like the entire date (mm/dd/yy) for each page to be in C1 of every page auto advancing. What am I missing to do so?
    Thank you in advance.

  37. Hi,
    I might have missed it, but I am trying to create a list of dates that starts on the 1/1/2023 and ends today, and that updates the "today" avery time the spreadsheet is open (ie incrementally adds the days as the year goes by). Is that possible?

    • Hi! Determine the number of the day in the current year and create a sequence of dates as described in the article above
      If I got you right, the formula below will help you with your task:

      =SEQUENCE(TODAY()-EOMONTH(TODAY(),-MONTH(TODAY())),1,EOMONTH(TODAY(),-MONTH(TODAY()))+1,1)

      • Thank you it worked great! I am not sure I understand the logic behind this sequence but I'll keep trying :)

  38. I am trying to create a column sequence of dates by day among a few years. However, whenever I use the fill function to lets say pass from November 30th to the 1st of December, it automatically starts December with the 3rd and not the first. For instance:

    11/22/2007
    11/23/2007
    11/24/2007
    11/25/2007
    11/26/2007
    11/27/2007
    11/28/2007
    11/29/2007
    11/30/2007
    12/3/2007

    Note how the last one started form the 3rd

    • Hi! Unfortunately, it was not successful to reproduce your problem. Try using formulas to create a sequence of dates. There is all the information you need above in this article.

  39. Hi there,
    I would like to create a series of dates on columns across the top of a spreadsheet as they are reading infro from different tabs.

    e.g,
    3 Apr 23 3 Apr 23 3 Apr 23 10 Apr 23 10 Apr 23 10 Apr 23 17 Apr 23 17 Apr 23 17 Apr 23 and so on

    Therefore repeating the date 3 times across the top then going up by one week and repeating the same pattern I hop that makes sense

    Thanks in advance for your help.

  40. Hello sir,
    I need your help regarding daily attendance sheet monthly report .for example if "Friday" is weekend day and there are 4 or 5 "Friday" in a month. with daily attendance entry when ever Friday come automatically count as a payable day in salary with daily present and absent for salary calculation.
    in my excel sheet I have a problem there are 4 "Friday" in a month and when I enter even 1 " PRESENT" automatically 4 "Fridays" count as a payable day and result comes 5 day salary.
    I'm using this formula (AN14= daily presents)

    =IF(AN14=0,0,COUNTIF($F$8:$AJ$8,"Fri")+AN14)

    • Hi! Unfortunately, this information is not enough to recommend a formula to you. Please provide me with an example of the source data and the expected result.

      • Hi sir,
        For salary calculation i have to use formulas to separate days .. if any employee who works 27 days (30 days a month) means i have to give full salary and if that employee worked 28 days means i have to give full salary +1 day salary ... as like for 30 -3 days .. so which one is suited

  41. 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!

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

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

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

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

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

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

  48. 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?

  49. 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 ?

  50. 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!

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 :)