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 5. Total comments: 299

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

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

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

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

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

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

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

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

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

          1. Basically auto update a date every 28 days.

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

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

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

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

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

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

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

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

  10. what i do for get sequence of two numbers
    exp.
    1-2
    3-4
    5-6
    7-8

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  30. hi, if I need to make a calendar for each date 11 rows and row 12 will be subtotal for each date for the year 2022

  31. Hi!

    I need to generate a list of pay date ranges, starting with (1/1-1/15) and occurring every 2 weeks until Dec 2022. How do I do that?

    Thank you!
    T

    1. Hello!
      The information you provided is not enough to understand your case and give you any advice. Please provide me with an example of the 4-5 expected results.

      1. Thank you for replying! It may be easier to email you a file. Can I do that?

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

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

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

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

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

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

  38. Hello. I am using a calendar template but was wondering if there was a way to use information from one spreadsheet to auto-populate my calendar. I am trying to use the calendar to show when individuals on my team are available. Example
    Member 1 Available on July 23, 2021.
    Member 2 Available on July 23, 2021
    Member 3 Available on August 5, 2021

    Is there a way to do this?
    Thank you

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

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

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

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

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

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

  45. Hi thank you for sharing all of this knowledge

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

    Is this possible?

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

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

      You can copy this formula down along the column.

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

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

  48. "Type the initial date in the first cell.
    Select the cell with the initial date and drag the fill handle (a small green square at the bottom-right corner) down or to the right.
    Excel will immediately generate a series of dates in the same format as the first date that you typed manuall'

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

  49. This is so helpful, thank you!

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