Imagine a situation in which you need to create a date-wise table. For example, you are creating a schedule where you must mention a date-wise list of activities. In such a case, you would need a list of dates. Obviously, no list is unlimited, so your list would have a starting and ending date.
List all Dates between two dates in Excel
For such cases, you could use the method explained in this article to obtain the list of dates between two dates as a list in a column. You have two ways of proceeding:
- Create a list of sequential dates in Excel using the Fill handle
- Obtain a list of all dates between two given dates in Excel using 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. While it doesn’t exactly help with dates between two given dates, using this option is much easier than using the formula.
Simply Enter the date in say cell A1 and click anywhere outside the cell. Then click on the cell again to activate the Fill handle.
Pull the Fill handle down to create a list of sequential dates in Excel.
Obtain a list of all dates between two given dates in Excel using formula
The formula to obtain a list of all dates between two given dates in Excel is a little complicated. We will explain it using an example:
Let us suppose the starting date is mentioned in cell A1, and the ending date is mentioned in cell A2. You need to obtain the list in column C. The procedure for finding the list of dates would be as follows:
First, enter the formula and press Enter:
=A1+1 in cell C1
It will display the date next to the starting 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 cell C2 and then back on C2 to highlight the option to pull the formula down. Pull the formula down till you start getting blank results on the cells. Hit Enter to display the results.
The problem with this formula is that it would be hard to define the syntax or modify it. Furthermore, the dates aren’t exactly in order. The first and last dates remain in cells A1 and A2. The dates in column C are the ones between these dates.
The resolution to this problem could involve a bit of dragging, cutting, and pasting.
I hope this helps.
Now read: How to calculate the number of Leap Years between two dates in Excel.
