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

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. Continue reading →

Comments page 3. Total comments: 211

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

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

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

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

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

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

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

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

      1. Thanks

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

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

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

  9. If I type a date in,, AR3 and I want a date, seven days later to appear in BZ3, how would I do that... e.g. I type:
    24 October 2021 in cell AR3,,,
    30 October 2021 automatically appears in BZ3

      1. Simple, but indeed it! Thanks and I will read the materials you recommended.

  10. Hi
    I am trying to populate a column of cells with times ranging from 9:00 to 11.45 or 14:00 to 16:45 based on a selection from a drop down box showing Am or Pm option. How best to achieve this ?
    Thanks

  11. Thank you for these tips. They are of great help.

    Quick question, please - How would one attempt to list a sequence where dates are a little staggered? Say, 1/4/8/11/15/18/22/25/29 Oct followed by 1/5/8/12/15... Nov and so on?

    I tried setting the first cell as (A1) Oct 1, and set the one below as A1+3 and the one below that as A2+4, and so on. Didn't work. Likewise, I next tried A2=A1+3 and A3=A1+7, etc. That didn't work either.

    Appreciate your thoughts and tips :)

    1. Trying to see if, instead of going by date, going by "day" would work (choosing two or three specific days per week).

  12. I have a yearly calendar set up over 12 monthly tabs. How do I enter a recurring piece of information for a particular day every month please? For example every Wednesday: SWIMMING CLUB 20:00 - 22:00

  13. I want to create a sequential number based on every time a row is entered
    19/09/2021 – 25/09/2021
    26/09/2021 – 02/10/2021

    1. Hi!
      The formula below will do the trick for you:

      =TEXT(SEQUENCE(10,1,44458,7),"dd-mm-YYYY")&" - "&TEXT((SEQUENCE(10,1,44458,7)+6),"dd-mm-yyyy")

      1. I'd like to do this but across numerous columns, when I copy this formula into a cell it auto populates rows.

  14. Hello,

    I'm trying to use your Date Sequence - =_xlfn.SEQUENCE(B1, 1, B2, B3) and I keep getting a NAME error, regardless of which SEQUENCE formula from the above suggestions that I try. Is there an issue with the SEQUENCE formula no longer compatible with Excel?

    What I have is a column for the days of the week for a specific series of dates - I want that column to automatically changes the column of dates for a two week period (weekdays only) based on the Start Date that is input at the top of the page.

    Thank you for your help!

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

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

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

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

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

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

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

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

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

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

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

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)