How to Auto Populate Dates Between Two Given Dates in Google Sheets

I have two dates in a spreadsheet – one start date and another end date. I want now to populate all the dates between these two dates in a column or row. In Google Sheets, you can auto-populate dates between two given dates with an Array Formula.

I am talking about how to expand two dates in Google Doc Spreadsheets.

list dates between two dates in Google Sheets

In the above example, I have a start date in Cell A2 and an end date in cell B2.

With one single formula in cell D2, I’ve listed all the dates between these two dates, including the start and end dates. How?

Auto-Populate Dates Between Two Given Dates Vertically in Google Sheets

I have two combination-type formulas based on;

ROW and INDIRECT.
DAYS/DATEDIF and SEQUENCE.

The first solution is more common in use, or you may see advanced Google Sheets users using. Because one of the functions, i.e., the SEQUENCE, in the second combo is a relatively new function in Google Sheets.

Choose the one as you wish. Let’s go to the formulas.

1. ROW and INDIRECT Formula

Here is my first formula.

=ArrayFormula(TO_DATE(row(indirect("A"&A2):indirect("A"&B2))))

You can use this formula to get all the dates between two given dates vertically in Google Sheets. Here in this formula, A2 contains the start date, and B2 the end date.

Just change these cell references as per your start and end date in your sheet.

Formula Logic/Explanation

What will you do if you want to auto-populate continuous serial numbers (sequential numbers) in a column?

For example, I want serial numbers 1 to 50 in a Column. What I will do is using the ROW function in an Array as below.

=ArrayFormula(row(A1:A50))

This ROW formula will list the numbers 1 to 50 in the column where you apply it.

Here the value of Row(A1) is 1, and Row(A50) is 50.

Now see the below formula that uses the Indirect function.

=row(indirect("A"&A2))

If you use this formula, it will return 43191, the value of the date in cell A2.

How to expand start date and end date in google sheets

In line with the above, I am referring to cell B2 in the below formula.

=row(indirect("A"&B2))

This formula will return the number 43205. Now you can guess the rest, right?

See my master formula above, where I’ve used these two Row and Indirect combo functions to populate numbers between the numbers 43191 and 43205.

It’s the unformatted dates from 01/04/2018 to 15/04/2018. With the help of the To_Date function, I have converted these Date values into dates. That’s all.

The above is not the only solution to auto-populate dates between two given dates vertically in Google Sheets. Let’s go to a different formula.

2. DAYS/DATEDIF and SEQUENCE

Here is the formula to use.

=sequence(days(B2,A2)+1,1,A2)

Let me explain this formula in short with the help of its syntax, i.e., SEQUENCE(rows, columns, start).

rows: The number of days between the dates in cells B2 and A2 to return the result in that many rows.

columns: 1 (the number of columns in the result)

start: A2 (sequence starting from)

The result of the above formula will be date values (number format). To get date format, do as follows.

Select the result range.
Go to the menu Format > Number > Date.

I could use the To_date function with this formula to format the result automatically from number to date. But I have opted not to use it because it requires the ArrayFormula to expand.

In formula # 1, I have used To_date because the Row function in that anyhow requires the ArrayFormula.

The above is another solution to auto-populate information between two given dates in Google Sheets.

As a side note, if you want to replace Days with Datedif, use the below formula.

=sequence(datedif(A2,B2,"D")+1,1,A2)

Must Check: Learn Google Sheets Complete Date Functions

Auto-Populate Dates Between Two Given Dates Horizontally in Google Sheets

On certain occasions, you may want to populate the dates horizontally, like in the header on a Gantt chart bar area. So let’s see how to do that with the above two formulas.

In the first formula, I will use the TRANSPOSE function to change the orientation of the auto-populated dates. But the placing of the function is crucial. It should be as follows.

=ArrayFormula(TRANSPOSE(TO_DATE(row(indirect("A"&A2):indirect("A"&B2)))))

In the second formula, you can either use the Transpose or change the elements (reference/expression) slightly. I would prefer the latter one as below.

=sequence(1,datedif(A2,B2,"D")+1,A2)

Advanced Use of Expanding Dates in Google Sheets

You have learned how to auto-populate dates between two given dates in Google Sheets.

You can use the above tips, i.e., expanding dates between two dates, in Vlookup in Google Sheets to overcome one of its drawbacks! What’s that?

In Google Sheets, in an unsorted date column, the Vlookup can only lookup dates that are ‘physically available’ in the cells.

You can’t provide two dates and force Vlookup to lookup a date that comes between the provided two dates.

So in such cases, you can consider expanding the start and end dates as above and replicate the corresponding column values to use in Vlookup.

Still don’t get? See this advanced Vlookup tutorial exclusively on Info Inspired.

Thanks for the stay. Enjoy!

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,