excelpyxll

How could I generate a list of dates based on user input?

Say that I have a spreadsheet that allows users to put in some metadata like the following:

Date range start: mm/dd/yyyy
Date range end: mm/dd/yyyy
Mondays: (y/n)
Tuesdays: (y/n)
Wednesdays: (y/n)
Thursdays: (y/n)
Fridays: (y/n)

Based on that, I want to generate a list of dates in the format of mm/dd/yyyy that starts on 4/1/2019, ends on 4/30/2019 and only includes dates of the day that was indicated with a y.

So if a user put in start date = 04/01/2019, end date = 04/30/2019, y for just Mondays and Wednesdays, the list would look like:

04/01/2019
04/03/2019
04/08/2019
04/10/2019
04/15/2019
04/17/2019
04/22/2019
04/24/2019
04/29/2019

Couldn't find an Excel function to start with. I don't know VBA but imagine it would be possible to do this with that.

If I wanted to write this in Python by using an add-in like Pyxll, would everyone with a copy of the Excel file be required to install Pyxll?

Solution

  • @simplycoding: VBA doesn't have a function or suroutine ready to do what you are looking for right out of the box. However, you can compose your own ones based on whatever VBA has on offer, which is a lot.

    This is my startup scenario:

    enter image description here

    I wrote, tested and commented the following SUB in some 20 minutes. Believe me when I say I'm not a first row VBA coder.

    Sub DateList()
'(C) Antonio Rodulfo, 2019
Dim dEnd, dStart, dCounter As Date
Dim iaDays(1 To 5) As Integer
Dim iCounter, iRow As Integer
'   Indent your sentences properly
    '   Reading parameters
    dStart = Cells(2, 1).Value
    dEnd = Cells(2, 2)
    For iCounter = 1 To 5
        If Cells(2, 2 + iCounter) = "y" Then
            iaDays(iCounter) = 1
        Else
            iaDays(iCounter) = 0
        End If
    Next iCounter
    '   Here's where the list of dates will start
    iRow = 4
    '   I process the dates: Excel stores dates in its own
    '       coding, which we can use to run a for..next loop
    For dCounter = dStart To dEnd
        '   Weekday(datecode,type) returns the day of week
        '   type 2 means the week starts with  monday being day 1
        iCounter = Weekday(dCounter, 2)
        '   The sub only sets dates for working days
        '   monday to friday
        If iCounter <= 5 Then
            '   date must be set if found "y" in the matching day
            If iaDays(iCounter) = 1 Then
                '   I like setting the proper numberformat so
                '   that no surprises are found when reading it
                Cells(iRow, 1).NumberFormat = "dd/mmm/yyyy"
                Cells(iRow, 1) = dCounter
                '   Increasing iRow sets the focus on next row
                iRow = iRow + 1
            End If
        End If
    '   Adding the index name to the next sentence helps
    '   tracking the structures
    Next dCounter
End Sub

    I always recommend using Option Explicit when coding. It can seem annoying at first, but it will help you a lot when testing and debugging your code.

    Good luck!