Return Work Dates between start date and end date

David Clemons

Hi,

I have 2 columns, [Start Date] and [End Date] on my sheet. I want to fill another column [All Dates] with all dates between and included start and end date. So if: Start Date - 8/24/2020, End Date - 8/28/2020, i would like the return values in a column [All Dates] to be: 8/24/2020, 8/25/2020, 8/26/2020, 8/27/2020, 8/28/2020.

How can I loop through while less than [End Date], then combine using Join.

Paul Newcome

Ok. There is no really easy way of doing this. The easiest to manage solution I can think of would be to create a table of dates that will cover all of the working dates on your sheet.

The most efficient way to get that built would be to use a basic MIN function with a cross sheet reference to pull the earliest starting date.

Then you can use the WORKDAY function to populate the remaining dates.

In your BetweenDates column you would then use

=JOIN(COLLECT({Dates Sheet Range of Dates}, AND(@cell >= [Start Date]@row, @cell <= [End Date]@row)), ", ")

David Clemons

Created another sheet to reference dates, so your solution worked great. Thanks for the help.

Paul Newcome

Can you provide some screenshots that contain manually entered data showing step by step what you are wanting to accomplish?

David Clemons

Here is a screen shot.

I typed in manually the BetweenDates, but that is what i am looking for.

Paul Newcome

Ok. There is no really easy way of doing this. The easiest to manage solution I can think of would be to create a table of dates that will cover all of the working dates on your sheet.

The most efficient way to get that built would be to use a basic MIN function with a cross sheet reference to pull the earliest starting date.

Then you can use the WORKDAY function to populate the remaining dates.

In your BetweenDates column you would then use

=JOIN(COLLECT({Dates Sheet Range of Dates}, AND(@cell >= [Start Date]@row, @cell <= [End Date]@row)), ", ")

David Clemons

Thank you for the thoughts...I will give this a try. I was hoping not to make an additional.

David Clemons

Created another sheet to reference dates, so your solution worked great. Thanks for the help.

Paul Newcome

Happy to help. 👍️