Auto-Populate Dates Between Two Given Dates in Google Sheets

This tutorial explains how to auto-populate dates between two given dates in Google Sheets using simple formulas.

Let’s dive into the details.

Scenario

Suppose you have a start date in cell A2 and an end date in cell B2. You want to populate all the dates, including the start and end dates, in a column or row.

This guide provides two solutions:

Using ROW and INDIRECT functions.
Using DAYS/DATEDIF and SEQUENCE functions.

Auto-Populate Dates Vertically

Solution 1: ROW and INDIRECT Formula

Use the following formula in cell D2:

=ArrayFormula(
   TO_DATE(
      ROW(INDIRECT("A"&A2):INDIRECT("A"&B2))
   )
)

Auto-populating dates between two given dates vertically in Google Sheets

Explanation:

ROW(INDIRECT("A"&A2):INDIRECT("A"&B2))
This generates a sequence of serial numbers between the numeric representations of the dates in A2 and B2.
- ROW(INDIRECT("A"&A2)): Returns the numeric value of the start date.
- ROW(INDIRECT("A"&B2)): Returns the numeric value of the end date.
TO_DATE
- Converts these numeric values into date format.
ARRAYFORMULA
- Expands the formula to handle an array of values because the ROW function by itself does not process arrays automatically.

This formula efficiently auto-populates all dates between the given start and end dates, including both. Adjust the references (A2 and B2) as needed for your dataset.

Solution 2: DAYS/DATEDIF and SEQUENCE Formula

Use the following formula in cell D2:

=SEQUENCE(DAYS(B2, A2)+1, 1, A2)

Explanation:

DAYS(B2, A2)
Calculates the total number of days between the two dates.
SEQUENCE(rows, columns, start)
- rows: Number of days + 1 (to include both start and end dates).
- columns: 1 (dates listed vertically).
- start: The starting date in A2.

Optional Adjustment

If you prefer to use DATEDIF instead of DAYS, replace DAYS(B2, A2) with:

DATEDIF(A2, B2, "D")

Formatting Dates

By default, formulas may return date serial numbers. To format them as dates:

Select the output range.
Go to Format > Number > Date.

Alternatively, wrap the SEQUENCE formula in TO_DATE:

=ArrayFormula(
   TO_DATE(
      SEQUENCE(DAYS(B2, A2) + 1, 1, A2)
   )
)

Auto-Populate Dates Horizontally

For horizontal date population (e.g., Gantt chart headers), modify the formulas:

ROW and INDIRECT Formula

=ArrayFormula(
   TRANSPOSE(
      TO_DATE(
         ROW(INDIRECT("A"&A2):INDIRECT("A"&B2))
      )
   )
)

SEQUENCE Formula

=SEQUENCE(1, DAYS(B2, A2) + 1, A2)

Key Differences Between the Two Methods

When it comes to auto-populating dates between two given dates in Google Sheets, the SEQUENCE-based method is easier to understand and use. Its straightforward syntax allows you to generate dates quickly with fewer steps, making it a popular choice for modern users.

The ROW-based method is included because it was widely used before the SEQUENCE function became available. This approach combines functions like ROW, INDIRECT, and TO_DATE, which can make it more complex and less intuitive. However, you might still encounter it in older files or resources, as it was the go-to method for auto-populating dates between two given dates in earlier versions of Google Sheets.

In summary:

The SEQUENCE method is user-friendly and ideal for most use cases today.
The ROW-based method is better for compatibility with older versions or understanding legacy approaches.

Both methods effectively auto-populate dates between two given dates, so the choice depends on your preference or the specific situation.

23 COMMENTS

puja May 3, 2023 At 11:15 pm

Below is the data:

Timestamp | Email Address | Ldap | Leave start date | Leave End Date
5/3/2023 14:43:16 | “abcd@gmail.com” | abcd | 5/1/2023 | 5/4/2023

And I want the result to be like this:

Timestamp | Email Address | Ldap | Leave start date
5/3/2023 14:43:16 | “abcd@gmail.com” | abcd | 5/1/2023
5/3/2023 14:43:16 | “abcd@gmail.com” | abcd | 5/2/2023
5/3/2023 14:43:16 | “abcd@gmail.com” | abcd | 5/3/2023
5/3/2023 14:43:16 | “abcd@gmail.com” | abcd | 5/4/2023

Please help me, it would be a great help.

- Prashanth May 4, 2023 At 3:07 pm
  
  Hi, Puja,
  
  I’m unsure whether you have just one entry or multiple entries.
  
  I assume you have your data in a single row in A2:E2, as per the given format.
  
  To expand the first three columns, i.e., Timestamp, Email Address, and Ldap, you can use the below formula.
  
  =ArrayFormula(chooserows(A2:C2,sequence(datedif(D2,E2,"M")+1)^0))
  
  To expand the Leave start date column, use the below one.
  
  =ArrayFormula(edate(D2,sequence( datedif(D2,E2,"M")+1)))
  
  For further assistance, if any, please share a sample Sheet.
  
Richard Semper October 26, 2022 At 1:28 am

Hi Prashanth,

Your information so far has really helped me.

I am trying to accomplish a similar task but with some added complexity.

I have a start date (column A) and end date (column B) and need to fill in all of the dates between each start date and end date into one column, but to also leave blank any rows in that column that are not between any of the start or end dates (see illustration below)

The purpose of my task is to show by date when a ‘container’ will be in use or empty by looking at the output column.

Start Date | End Date
01/01/2022 | 03/01/2022
06/01/2022 | 9/01/2022
12/01/2022 | 14/01/2022

Output

01/01/2022
02/01/2022
03/01/2022
BLANK
BLANK
06/01/2022
07/01/2022
08/01/2022
09/01/2022
BLANK
BLANK
12/01/2022
…
…

Thank you for any insight you may give.
Richard

- Prashanth October 26, 2022 At 9:19 am
  
  Hi, Richard Semper,
  
  I can clearly understand your requirement. Thanks for your example.
  
  This formula would help you achieve your requirement if the start dates are in A2:A100 and the end dates are in B2:B100.
  
  =ArrayFormula(xlookup(sequence(days(max(B2:B100),min(A2:A100))+1, 1,min(A2:A100)),flatten(split(join("|",map(A2:A100,B2:B100,lambda(a,b, if(and(a<>"",b<>""),join("|",sequence(1,days(b,a)+1,a)),)))),"|")), flatten(split(join("|",map(A2:A100,B2:B100,lambda(a,b, if(and(a<>"",b<>""),join("|",sequence(1,days(b,a)+1,a)),)))),"|"))," ",0))
  
  Select the result and apply Format > Number > Date.
  
Wyn davies May 14, 2022 At 4:03 pm

Hi, this is helpful. Thanks.

Is it possible to modify the interval into half days (am & pm) between 2 dates, resulting in 2 rows per day?

Is it possible? Thanks

- Prashanth May 15, 2022 At 5:26 pm
  
  Hi, Wyn davies,
  
  B1 – start date
  B2 – end date
  
  The formula in C1.
  
  =ArrayFormula(B1+SEQUENCE((days(B2,B1)+1)*2,1,0)/2)
  
  Now you must format C1:C. Go to Format > Number > Custom number format and enter dd/MM/yyyy H:mm:ss am/pm in the given field.
  
  If that formatting doesn’t help, try dd/MM/yyyy am/pm.
  
Ash June 9, 2021 At 7:20 pm

Great information, thank you. Is there a way to have the dates go across columns as opposed to down a column? I am looking for something that could be used to schedule employees similar to a Gantt chart.

- Prashanth June 10, 2021 At 11:18 am
  
  Hi, Ash,
  
  Please use the Transpose with my formula. Considering your request, I have updated my post.
  
SER June 24, 2020 At 7:34 pm

I Want two click buttons and auto-fill date.

Bill April 16, 2020 At 3:01 pm

Thanks a lot. This is great and works for me. However, I want to understand it better. What is the “A” for in the indirect functions? I notice that it works if I replace this with other values (eg “B” or “E”) but not if I make it an empty string “”. I am just trying to understand the required syntax better. Thanks a lot.

- Prashanth April 17, 2020 At 12:36 pm
  
  Hi, Bill,
  
  The formula =ArrayFormula(row(A1:A10)) will return the numbers 1 to 10. The same is the case with the below formula.
  
  =ArrayFormula(row(B1:B10))
  
  So it doesn’t matter which column you specify. The sequential numbers are based on rows, not columns.
  
  In this, I replaced 1 with the start date and 10 with the end date. For that, I have used the INDIRECT. So the output will be the sequential dates from the start date to the end date.
  
JASON October 16, 2019 At 10:15 am

It doesn’t seem to work if I want UK date format i.e DD/MM/YY no matter what I try.

- Prashanth October 17, 2019 At 8:03 am
  
  I have used the UK date format in my example and it works for me.
  
  The issue with the formula on your Sheet may be due to invalid date formats.
  
  Test the date entered in cell A2 with this formula.
  
  =ISDATE(A2)
  
  and B2 with this.
  
  =ISDATE(B2)
  
  If both the formulas return TRUE, then you won’t find any issue with my formula.
  
  Here is an alternative formula to return dates between the given start and end dates.
  
  =sequence(days(B2,A2)+1,1,A2)
  
  You will get the output in date values. Select the populated dates and format it to date from the Format menu.
  
  Dates based on start and end dates that without using any cell reference:
  
  =sequence(days(date(2019,10,25),date(2019,10,17))+1,1,date(2019,10,17))
  
  Here the start date is 17/10/2019 (DD/MM/YYYY) and end date is 25/10/2019 (DD/MM/YYYY)
  
MJ October 2, 2019 At 2:15 am

Is there a way to complete this with only weekdays?

- Prashanth October 2, 2019 At 8:00 am
  
  Hi, MJ,
  
  Here is the formula.
  
  =filter(TO_DATE(row(indirect("A"&A2):indirect("A"&B2))), regexmatch(TO_TEXT(weekday(TO_DATE(row(indirect("A"&A2):indirect("A"&B2))))), "1|7")=FALSE)
  
  If you want me to explain, I may consider writing a tutorial to explain this.
  
  Best,
  
- Prashanth October 9, 2019 At 12:25 pm
  
  See this too
  
  https://infoinspired.com/google-docs/spreadsheet/how-to-populate-sequential-dates-excluding-weekends-in-google-sheets/
  
  - Alex October 9, 2019 At 7:23 pm
    
    Hi Prashanth,
    
    Sorry, but I couldn’t post my reply under your last one.
    
    So thanks again for your help, but we are not quite there yet. Let me explain my complete challenge and not only one part of it.
    
    I would like to automatically transform my following campaign overview:
    
    Campaign Start_date End_date
    Campaign A 01/01/2019 15/01/2019
    Campaign B 05/01/2019 18/01/2019
    Campaign C 09/02/2019 12/02/2019
    … … …
    
    So that it looks like this:
    Campaign Date
    Campaign A 01/01/2019
    Campaign A …
    Campaign A 15/01/2019
    Campaign B 05/01/2019
    Campaign B …
    Campaign B 18/01/2019
    Campaign C 09/02/2019
    Campaign C …
    Campaign C 12/02/2019
    … …
    
    I am not even sure if the arrayformula is the best approach to this problem but I am happy for every suggestion you have 🙂
    
    Thanks in Advance!
    
    Best,
    
    Alex
    
    - Prashanth October 10, 2019 At 7:00 pm
      
      Hi, Alex,
      
      You want to expand the start and end dates in each row as well as the associated text value in another column.
      
      I could manage to do it with a non-array formula. In my sheet, all the green color highlighted cells contain formulas.
      
      The final output is in Sheet2.
      
      https://docs.google.com/spreadsheets/d/1KJSbAA6gZrdIkb79h7yOXup4FQEVPZEzgnacpkCb5UE/copy
      
      The formula might stop working if the number of rows is more than 1000 (approx) as the Textjoin function used has such a limitation.
      
      Best,
      
Joseph August 2, 2018 At 10:24 pm

This is great! Now, what if I wanted to do this for a list? I want to create an array for when columns A and B keep going. How would you do that?

- Alex October 9, 2019 At 11:43 am
  
  Hi Joseph,
  
  I have the same issue. Does anyone have a solution for that?
  
  That would be really awesome.
  
  Best Regards,
  
  Alex
  
  - Prashanth October 9, 2019 At 12:31 pm
    
    Hi, Alex,
    
    Do you want to repeat the same for dates in each row? Then enter this formula in cell C2 and copy it down.
    
    =ArrayFormula(transpose(TO_DATE(row(indirect("A"&A2):indirect("A"&B2)))))
    
    Best,
    
    - Alex October 9, 2019 At 1:04 pm
      
      Hi Prashanth,
      
      thanks for your very quick response. 🙂
      
      No, I didn’t want to transpose it. It’s more like if you got many different dates ranges in Column A and B like this:
      
      Start date End date
      01/01/2019 15/01/2019
      05/01/2019 18/01/2019
      09/02/2019 12/02/2019
      
      And I want to auto-populate them in one column with every date in between like this:
      
      01/01/2019
      02/01/2019
      …
      14/01/2019
      15/01/2019
      05/01/2019
      …
      18/01/2019
      09/02/2019
      …
      12/02/2019
      
      But not only three date ranges like in my example but X amount of times.
      
      It’s kind of difficult to explain, but I hope you see what I mean.
      
      Best,
      Alex
      
      - Prashanth October 9, 2019 At 2:55 pm
        
        Hi, Alex,
        
        First, enter the start dates and end dates in one column and sort it.
        
        Then follow the instructions given in my earlier post here – Find Missing Sequential Dates in a List in Google Sheets [Array Formula].
        
        I hope that will help you to solve the problem. If not please let me know, I will try to write the formula that suits your specific need.
        
        Best,