Excel making list of days between two dates


Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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)

Dates drag and drop - Excel making list of days between two dates

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.

img 5e5caba08a609 - Excel making list of days between two dates

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.

img 5e5cabbcbaefd - Excel making list of days between two dates

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
subs profile e1563205311409 - Excel making list of days between two dates
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address