How to make a list of specific dates between the start and end dates that are in two columns and multiple rows
For this Excel tutorial I have chosen the theme of which is linked to two dates ('start' and 'end' dates) contained in two columns. Our goal is to display all the dates in a column that are missing between two dates. 'Start' and 'End' dates are in multiple rows in two columns. In the image below, see the 'Start' dates in the 'A' column, 'End' dates are in the 'B' column. Note that there is only a 'start' date in each row but no ending 'end' because some process is not complete.
In the 'D' column we want to display a list of all the missing dates between the 'start' and 'end' dates. The 'D' column returns a list of all unique dates without duplicates.
Extract all dates between two dates in multiple rows
Based on the Start & End date you see in Figure 1 above, I have created a cell range in which I displaying all the missing dates between two dates in the same row. In Figure 2 below, I set the maximum number of dates in one month. In this way, the number of days 1 to 31 is set which can be maximum in one month. These dates/days will serve as a condition for the other formulas that are located in multiple rows below. This is 'helper range'.
The first date of the month is set in 'F1' cell. The cell is formatted so that it only displays the number of days.
In the 'G1' cell, the following formula is set. (This formula is copied to the right to the last column)
In Image 2 above you see a list of all the missing date in the same row. This date list is based on the same row (see Figure 1), which contains two dates 'Start & End'. So in the second row, all the dates between the 'Start' and 'End' dates are returned. Furthermore, in all other rows also displays a list of all the missing date from the same row. Everything is resolved by an Excel formula.
The 'F2' cell, is the following formula. (Copy this form to the right to the last column 'AJ1', then down to the last row 'AJ14')
So you can notice the formula above is based on three conditions. These conditions are 'Start date', 'End date', 'Value as number of day' in the first row.
Displays a list of all dates from cell ranges to one column
Now, when we have a list of all the missing days between two dates, we can access the creation of a date list between two dates from two columns. In Figure 3 below, notice all dates between two dates that are in two columns and multiple rows. This list is a list of Unique Dates that is created based on cell ranges 'F1:AJ14'.
The ARRAY formula in 'D2' cell is the following. (copy the formula to the last desired order)
Notice the mathematical expression '5^5' in the formula above. This term I have set up this chance. It may be different, but it is important that its result is a large number (high value). So it can be '6^6' or '7^7' etc. If its result is a low number then the formula will return an empty cell or a error.
To make it easier to understand how a formula works, look at the Evaluate Formula or use the F9 key for the result within the formula. And finally, using this Excel formula, we can create a list of all dates between two dates, two columns, and multiple rows without using the Power Query.
Attached to this Excel tutorial you have the option to download ZIP files (A list of all dates between two dates) that contains the Workbook used while I was making this tutorial. Sorry on my English gramatical errors.
Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.