VBA to return list of dates between start and end date

Hudson512

New Member
Joined
Mar 21, 2011
Messages
8
Office Version
  1. 365
Hi,

First off, let me start by saying I have very little experience in VBA so apologies in advance!

I am hoping someone could help me with a code that pulls all the dates listed between two cells (start and end date).

I have tried a couple which seem to work but I can't work out how to do a couple of things:

1. Have the dates listed horizontally rather than vertically
2. Replace the listed dates automatically when the start / end date is updated

Any help would be greatly appreciated.

Thanks
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,029
Office Version
  1. 365
Platform
  1. Windows
I guess it's mostly syntactic sugar and they're really just fancy loops, but Python and Javascript have ways to make one-liners like you're so adept at.

JS:
Code:
ar = Array.from({length:days},(v,k)=>new Date(sDate.setDate(sDate.getDate() + k)).toLocaleString())

Python:
Code:
ar = [sDate + datetime.timedelta(days=x) for x in range(0, (eDate-sDate).days)]
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,029
Office Version
  1. 365
Platform
  1. Windows
Actually, the easiest one, and one I had in my head when I made the comment, is how Power Query does it.

The line of M Code to generate the list of dates is just.

Power Query:
Table.AddColumn(Source, "Custom", each {Number.From([StartDate])..Number.From([EndDate])})

Book1
CDEF
3StartDateEndDateCustom
41/1/20211/15/20211/1/2021
51/2/2021
61/3/2021
71/4/2021
81/5/2021
91/6/2021
101/7/2021
111/8/2021
121/9/2021
131/10/2021
141/11/2021
151/12/2021
161/13/2021
171/14/2021
181/15/2021
Sheet1
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
In relation to a vba solution I would propose the code below. It addresses a number of issues that I see with one or both of the other codes
  • It will not error under the following circumstances
    • If start date or end date cells are empty.
    • If a non-date value is accidentally entered in start or end date cells. (There a re a few very rare exceptions to this but I doubt they will practically occur)
  • It will not error or enter a list of (presumably incorrect) dates if End date is earlier than Start date
  • It won't clear the entered start and/or end date if there is not already data in columns C:?? (eg when filling in start/end dates for the first time
  • It won't needlessly re-call itself when values are cleared from or entered into the sheet by the code.
I am aware that some of the issues above may have already been addressed via Data Validation, but mentioned just in case they are relevant.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rStart As Range, rEnd As Range

  Set rStart = Range("A2")
  Set rEnd = Range("B2")
  If Not Intersect(Target, Union(rStart, rEnd)) Is Nothing Then
    Application.EnableEvents = False
    Range("D2", Range("D2").End(xlToRight)).ClearContents
    If IsDate(rStart.Value) And IsDate(rEnd.Value) And rEnd.Value >= rStart.Value Then
      Range("D2").Value = rStart.Value
      Range("D2").DataSeries xlRows, xlChronological, xlDay, 1, rEnd.Value
    End If
    Application.EnableEvents = True
  End If
End Sub

Hudson512.xlsm
ABCDEFGHI
1Start DateEnd Date
215/01/202119/01/202115/01/202116/01/202117/01/202118/01/202119/01/2021
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,122,829
Messages
5,598,343
Members
414,232
Latest member
MIA10_KO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top