Imagine a situation in which you have to create a table by date. For example, you create a calendar in which you have to mention a list of activities by date. In such a case, you would need a list of dates. Obviously, no list is unlimited, so your list would have a start and end date.
List all dates between two dates in Excel
In this case, you can use the method explained in this article to get the list of dates between two dates as a list in a column. There are two ways to do this:
- Create a list of sequential dates in Excel using the Fill Handle
- Get a list of all the dates between two given dates in Excel using a formula.
1) Create a list of sequential dates in Excel using the Fill Handle
Microsoft Excel has a fill handle, which makes it easy to create a list of sequential dates. Although this does not exactly help the dates between two given dates, using this option is much simpler than using the formula.
Just enter the date in cell A1 and click anywhere outside the cell. Then click again on the cell to activate the copy handle.
Lower the fill handle to create a list of sequential dates in Excel.
Get a list of all dates between two given dates in Excel using a formula
The formula to get a list of all the dates between two dates given in Excel is a little complicated. We will explain it with the help of an example:
Assume that the start date is mentioned in cell A1 and that the end date is indicated in cell A2. You must obtain the list in column C. The procedure for finding the list of dates would be:
First, enter the formula and press Enter:
=A1+1 in cell C1
It will show the date next to the start date in cell C1.
Now, type the following date in cell C2:
=IF($A$1+ROW(A1)>=$A$2-1,"",C1+1)
Click anywhere outside of cell C2, then return to C2 to highlight the option to scroll the formula. Pull the formula until you get blank results on the cells. Press Enter to view the results.
The problem with this formula is that it would be difficult to define or modify the syntax. Moreover, the dates are not quite correct. The first and last dates remain in cells A1 and A2. The dates in column C are those between these dates.
Solving this problem might involve a bit of slipping, cutting and pasting.
I hope this helps you.