Making a list of all the days between two Excel dates is possible in various ways, manually, an Excel 365 formula or some VBA code.
We need a way to make a list of all the dates between a start and end date. Use this to make a list of all vacation days for a fully working Networkdays() or Networkdays.Intl().
Drag n Drop
The simple manual option is to enter a start date then drag that cell down (or across), Excel will fill cells adding a day at a time.
Excel dates are raw numbers, all it’s doing is adding one to that number. The serial date number (Col F) appears as a date format (Col E)
Sequence() Function
In Office 365 that’s easy because of dynamic arrays include the Sequence() function.
Sequence() creates an array of values from a start value for the number of values you ask for, using any increment you like.
Since Excel dates are whole numbers, all you need to do is ask Sequence() to insert an array from a Start Date to an End Date.
Sequence(1 + EndDate – StartDate, 1, StartDate) the formula will spill the dates into the cells below.
VBA user defined function
For Excel 2019, Excel 2016 and earlier we need to turn to VBA.
Here’s a UDF (user defined function) that will do the job for both Excel 365 and earlier.
DateListArray() works for Excel 365 using Sequence(). We made this UDF to make NetworkDays() easier to read and understand.
For other Excel versions, comment out the Excel 365 line and de-comment the lines indicated.
Excel’s NetworkDays() and Holidays in the real world
Multiple Holiday lists in Networkdays()
Using NetworkDays.intl to count working days step-by-step
Excel convert Range to Array in VBA
Excel merge arrays into a single array with VBA
Public Function DateListArray(StartDate As Date, EndDate As Date) As Variant ' A tidy version of using Sequence() to get all days between two dates. ' Requires Excel 365 for Sequence() ' If necessary, use the alt. code for Excel 2019/2016 etc. Dim arr1 As Variant, i As Long ' Excel 365 dynamic arrays solution arr1 = Application.WorksheetFunction.Sequence(1 + EndDate - StartDate, 1, StartDate) ' Excel 2019/2016 etc solution ' For i = 0 To EndDate-StartDate ' ReDim Preserve arr1(i) ' arr1(i) = i + StartDate ' Next DateListArray = arr1 End Function
