Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in


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 good idea3

Compare Ranges utility (can compare two ranges, and select and color the same values or different values.)

doc compare ranges

Here I introduce formulas which can quickly list all dates between two given dates for you in Excel.

formula Never need to worry about long long formulas in Excel anymore! Kutools for Excel's Auto Text can add all formulas to a group
as auto text, and liberate your brain! Click here to know Auto Text     Click here to get free trial

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:

doc-list-all-dates-between-two-dates-3       doc-list-all-dates-between-two-dates-4

Then you can see all the dates between two given dates are listed in the column.


In the above formulas, A1 is the starting date, A2 is the ending date, and C1 is the first date among the date range.

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()
	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
	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:

doc-list-all-dates-between-two-dates-7          doc-list-all-dates-between-two-dates-8

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:

doc-list-all-dates-between-two-dates-9         doc-list-all-dates-between-two-dates-10

Note: The list generated by this VBA includes the starting date and ending date.

Actually, if you installed Kutools for Excel – a handy add in tool, you also can use the Insert Random Data to solve this problem.

Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

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:
doc insert random data 1

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:

doc-list-all-dates-between-two-dates-13           doc-list-all-dates-between-two-dates-14

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:

doc-list-all-dates-between-two-dates-15          doc-list-all-dates-between-two-dates-16

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.

You maybe interested in this:

Convert to Date

In some times, you can have multiple nonstandard dates in your worksheet, and to convert all of them to the standard date mm/dd/yyyy maybe troublesome for you. Here Kutools for Excel's Conver to Date can quickly convert dates to standard date as mm/dd/yyyy format.
doc convert date

Relative Articles:

Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Deuf · 1 years ago
    How to loop this vba code (1000 rows for example) ?
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      Sorry for reply such late, but I do not understand your question, the VBA only for listing dates between a date range, why need to loop the code?
  • To post as a guest, your comment is unpublished.
    NewSP · 1 years ago
    Firat - did you solve your issue? I have exactly the same issue and I cannot get the result in the row instead of the column.
    • To post as a guest, your comment is unpublished.
      Deuf · 1 years ago
      If you invert this line you can make it work :

      OutRng.Offset(ColIndex, 0) = i to OutRng.Offset(0, ColIndex) = i
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      Why do not try to transpose the column result to row?
  • To post as a guest, your comment is unpublished.
    singaravelu · 1 years ago
    i tried the VBA code it worked.. Thanks for sharing. Similarly is it possible to pase it along columns/ horizontally?
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      Hello, if you want to list dates in a row horizontally, you just need to use the vba code to list the dates, and copy the results and paste transpose.
  • To post as a guest, your comment is unpublished.
    Firat · 1 years ago

    Thanks for sharing a great code. I would like to ask one question though. I am using this VBA code you shared.

    1) Can I list all the other cells in the same row with the dates?
    2) Can we define the starting date cell and ending date cell and the cell that the new information will be written?

    I am asking these questions because I have 30 rows. Each row has data for different people. Cell G is a starting date and Cell H is an ending date. Other cells contains some information. I would like this to be listed in a new cell as all the dates between these cells. For example (just showing demonstration, so only G and H cells written below-I is where the list appears):

    Row 2 Person A 28/05/2017 05/06/2017 28/05/2017
    Row 3 Person A 28/05/2017 05/06/2017 29/05/2017
    Row 4 Person A 28/05/2017 05/06/2017 30/05/2017
    Row 5 Person A 28/05/2017 05/06/2017 31/05/2017
    Row 6 Person A 28/05/2017 05/06/2017 01/06/2017
    Row 7 Person A 28/05/2017 05/06/2017 02/06/2017
    Row 8 Person A 28/05/2017 05/06/2017 03/06/2017
    Row 9 Person A 28/05/2017 05/06/2017 04/06/2017
    Row 10 Person A 28/05/2017 05/06/2017 05/06/2017
    Row 11 Person B 23/05/2017 31/05/2017 23/05/2017
    Row 12 Person B 23/05/2017 31/05/2017 24/05/2017
    Row 13 Person B 23/05/2017 31/05/2017 25/05/2017
    Row 14 Person B 23/05/2017 31/05/2017 26/05/2017
    and so on...
  • To post as a guest, your comment is unpublished.
    Kamal Kumara · 2 years ago
    Can we use text box instead of in box in macro