The purpose of the LIST_ALL_DATES custom-named function is to list all dates between a start date(s) and an end date(s) in Google Sheets.

This function also supports assigning values from another cell or range corresponding to the start date(s) and end date(s).

The following examples may help you understand the purpose of this named function which you can download to your sheet and use.

In the below examples, only the Cyan highlighted cells contain the formulas.

Examples:

1. List all dates between a start date (A3) and an end date (B3).

2. List all dates between multiple start dates (E3:E5) and end dates (F3:F5).

3. Start Date (A3), End Date (B3), and Additional Value (C3).

4. Start Dates (G3:G5), End Dates (H3:H5), and Additional Values (I3:I5).

In example 4, I’ve specified additional values from a single range, but the function supports multiple ranges.

Syntax and Arguments of the LIST_ALL_DATES Named Function

Syntax:

LIST_ALL_DATES(start_date_range, end_date_range, additional_range)

Arguments:

start_date_range: A date or date range that contains the start date(s). If it’s a range, it should be a single column, e.g., A2 or A2:A.

end_date_range: A date or date range that contains the end date(s). If it’s a range, it should be a single column, e.g., B2 or B2:B.

additional_range: The cell or range that contains the value(s) to assign to the list of dates, e.g., C2 or C2:C.

In this last argument, you can include more than one column.

In that scenario, you should combine them using the ~ delimiter, e.g., C2&"~"&D2 or C2:C&"~"&D2:D. Additionally, use the ArrayFormula function.

If you don’t want to use the last argument, specify a blank cell or blank range. We will see more about this in the examples below.

Let’s see the formulas to list all dates between two or more dates in Google Sheets.

Notes:

1. The formula will return date values. You must select the result and apply Format > Number > Date to retain the correct date format.
2. You can get the function from my example sheet below.
3. To know how to import the function and use it, please check Named Functions.

Example Sheet 281022

List All Dates Between Start and End Dates (Formula Examples) in Google Sheets

At the beginning of this post, I’ve shown four examples in the screenshots (image#1 and image#2).

Here are the formulas in use.

Example 1 (C3 Formula in image#1):

=LIST_ALL_DATES(A3,B3,"")

We have only the start date and end date in A3 and B3. So, I’ve specified "" in the third argument of the LIST_ALL_DATES formula.

Example 2 (G3 Formula in image#1):

=ArrayFormula(LIST_ALL_DATES(E3:E,F3:F,iferror(E3:E/0)))

Here also, we don’t want to use the last argument. So we must either specify a blank column, e.g., J3:J, or create a virtual blank column. I’ve followed the latter and that is iferror(E3:E/0).

I’ve used the ArrayFormula function since the virtual blank column demands that.

If you directly specify a blank column instead, you can use the LIST_ALL_DATES formula as below (please make sure that J3:J is blank).

=LIST_ALL_DATES(E3:E,F3:F,J3:J)

Example 3 (D3 Formula in image#2):

=LIST_ALL_DATES(A3,B3,C3)

Example 4 (J3 Formula in image#2):

=LIST_ALL_DATES(G3:G,H3:H,I3:I)

How Can We Specify More Than One Additional_Range in the Formula?

I’ve forecasted the same and included an option to include more than one additional column in my LIST_ALL_DATES Named Function in Google Sheets.

In the following example, we have start dates in A3:A, end dates in B3:B, tasks in C3:C, and man-hours in D3:D.

To list all dates, I’ve used the following formula in cell F3.

=ArrayFormula(LIST_ALL_DATES(A3:A,B3:B,C3:C&"~"&D3:D))

I’ve combined C3:C and D3:D using ~ delimiter as specified in the argument part of this tutorial.

You can similarly add more columns.

Related: Expand Dates and Assign Values in Google Sheets (Array Formula).