How to list all dates between two dates in Excel?
In some cases, if you have the specific starting date and the ending date, you may need to list all the dates between these two given dates in Excel. Now this tutorial is talking about the methods to list all dates between two dates in Excel.
List all dates between two dates by formulas
List all dates between two dates by Kutools for Excel 
List all dates between two dates by VBA
Comparison of Methods for Listing All Dates Between Two Dates in Excel
To list all dates between two specified dates in Excel, you can use formulas, Kutools for Excel's Insert Random Data feature, or VBA. All three methods can create a complete date list, but they differ in date coverage, output order, and setup.
| Comparison | Excel Formulas | Kutools Insert Random Data | VBA |
|---|---|---|---|
| Date coverage | Lists the dates between the specified start and end dates; the example excludes the two boundary dates | Uses the specified From and To dates with Workday, Weekend, and Unique Values enabled to generate all dates in the range | Lists every date from the starting date through the ending date, including both dates |
| Output order | Dates are generated sequentially | Dates are initially generated in random order and need to be sorted from oldest to newest | Dates are generated sequentially |
| Setup | Enter the start and end dates, create the formulas, and drag the fill handle down until a blank cell appears | Select a range, set the From and To dates, enable Workday, Weekend, and Unique Values, then sort the results | Add and run the VBA code, then select the start date, end date, and output location |
| Best for | Creating a sequential date list with standard worksheet formulas | Generating all unique dates in a specified range without formulas or VBA | Automatically creating a complete sequential date list through code |
List all dates between two dates by formulas
Here I introduce formulas which can quickly list all dates between two given dates for you in Excel.
1. Type the starting and ending dates into two cells, here I type them into cell A1 and A2. See screenshot:
2. Then go to cell C1 to type this formula =A1+1 into it, then click Enter key. See screenshot:
3. Then in cell C2, type this formula =IF($A$1+ROW(A1)>=$A$2-1,"",C1+1) into it, then drag the autofill handle down to the cells until a blank cell appears. See screenshots:
Then you can see all the dates between two given dates are listed in the column.
Note:
In the above formulas, A1 is the starting date, A2 is the ending date, and C1 is the first date among the date range.
List all dates between two dates by Kutools for Excel
Actually, if you installed Kutools for Excel – a handy add in tool, you also can use the Insert Random Data to solve this problem.
After free installing Kutools for Excel, please do as below:
1. Select a column you want to list dates between two dates, and click Kutools > Insert > Insert Random Data. See screenshot:
2. Then in the Insert Random Data dialog, click Date tab, then select the starting and ending dates from the From and To list, then remember to check Workday, Weekend and Unique Values checkboxes. See screenshot:
3. Click Ok to close the dialog, and another Kutools for Excel dialog pops out, just click Yes. Then you can see the dates between starting date and ending date are listed. See screenshots:
4. Now you need to sort the date list in an order you need. Click Data > Sort Oldest to Newest. Then you can see the dates are sorted from oldest date to newest date. See screenshots:
With Insert Random Data utility, you also can insert random integer, random string, and random time so on. Click here to know more about Insert Random Data.
List all dates between two dates by VBA
If you are interested in macro code, you can use the below VBA to list all dates between two given dates in Excel.
1. Type the starting date and ending date you into two cells, here I type in cell A1 and B1. See screenshot:
2. Press Alt +F11 keys to open Microsoft Visual Basic for Applications window.
3. Then click Insert > Module and copy and paste below VBA code to the popping Module window.
VBA: List all dates between two dates.
Sub WriteDates()
'Updateby20150305
Dim rng As Range
Dim StartRng As Range
Dim EndRng As Range
Dim OutRng As Range
Dim StartValue As Variant
Dim EndValue As Variant
xTitleId = "KutoolsforExcel"
Set StartRng = Application.Selection
Set StartRng = Application.InputBox("Start Range (single cell):", xTitleId, StartRng.Address, Type: = 8)
Set EndRng = Application.InputBox("End Range (single cell):", xTitleId, Type: = 8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type: = 8)
Set OutRng = OutRng.Range("A1")
StartValue = StartRng.Range("A1").Value
EndValue = EndRng.Range("A1").Value
If EndValue - StartValue <= 0 Then
Exit Sub
End If
ColIndex = 0
For i = StartValue To EndValue
OutRng.Offset(ColIndex, 0) = i
ColIndex = ColIndex + 1
Next
End Sub4. Click Run or F5 to run the VBA, and a dialog pops out for you to select the starting date, then click OK, then select the ending date in the second popping dialog. See screenshot:
5. Click OK, then select a cell to put out the dates, and then click OK. Now you can see all the dates between two dates are listed. See screenshots:
Note: The list generated by this VBA includes the starting date and ending date.
List all dates between two dates
Conclusion
Excel formulas provide a straightforward built-in solution for creating a sequential list of dates. In the method shown, you enter the start and end dates, use formulas to generate the dates between them, and drag the formula down until the end of the range is reached. This approach requires no add-ins or macros, but it involves formulas and manual filling.
Kutools for Excel's Insert Random Data offers a formula-free alternative. By specifying the From and To dates and enabling Workday, Weekend, and Unique Values, you can generate all unique dates across the specified range. Because Insert Random Data does not initially place the dates in chronological order, the generated list needs to be sorted from oldest to newest afterward.
VBA can automatically generate a complete sequential date list. The macro shown in the article asks you to select the start date, end date, and output location, and then writes every date in sequence, including both the starting and ending dates. This provides automated sequential output but requires VBA code and macro execution.
Relative Articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in