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 VBA

List all dates between two dates by Kutools for Excel

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 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 Sub

4. 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 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

Relative Articles:

• Create dynamic list of worksheet names

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

• Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Read More... Free Download... Purchase... 

---

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!

Read More... Free Download... Purchase...