Excel VBA / Formula . Fill a range of cells with each Friday of the current month

0
votes

So the titles a bit of a mess but im trying to do the following:

I have a range of cells from Q8:Q12, T8:T12, Q16:Q20 and T16:T20

Im trying to have these cells populate with the date for each friday of this current month. Essentially, using July (The current month) it would look something like this:

Q8/T8 = 06/07
Q9/T9 = 13/07
Q10/T10 = 20/07
Q11/T11 = 27/07
Q12/T12 = -

The reason Q/T12 would be blank is to handle months of the year that have 5 fridays in them rather than 4. Its kind of a way of error handling.

I have a cell that currently tracks the month within the Cell A9 and the formula looks like this:

=TEXT(NOW(),"mmmm")

Im not quite sure how to handle this logically really. Either VBA or a Formula would do in my eyes.

Ideally, because i have a different sheet for every month July, August etc. The formula above changes depending on what month it is currently. I would need to convert the Formula/VBA script from cells Q8:Q12 OR A9 into a value AFTER populating the date range cells Q8:Q12.

Anyone have any ideas. Im sorry its a bit of a messy question

3
vbaexcelexcel-formula
This is tagged with both VBA and Excel formula. Is there an implementation that you'd prefer of the two? – Comintern
In all honesty, i have no preference. If i was to choose one id probably go VBA as i can understand it a little easier than formulas. Whichever option is easier though – Batteredburrito

3 Answers

3
votes

Put this in the first cell and copy down 5

=IFERROR(AGGREGATE(15,6,ROW(INDEX(A:A,EOMONTH(TODAY(),-1)+1):INDEX(A:A,EOMONTH(TODAY(),0)))/(WEEKDAY(ROW(INDEX(A:A,EOMONTH(TODAY(),-1)+1):INDEX(A:A,EOMONTH(TODAY(),0))),1)=6),ROW(1:1)),"-")

Then format to your specifications.

There are probably more elegant formulas but this is what came to mind.

enter image description here

3
votes

Here is a macro version without needing a date value in Range("A9")...

Dim SoM As Date
Dim EoM As Date
Dim rw As Long

SoM = DateSerial(Year(Now), Month(Now) + 0, 1)

EoM = DateSerial(Year(Now), Month(Now) + 1, 0)

rw = 8
While SoM < EoM
    If Weekday(SoM) = vbFriday Then
        Cells(rw, 17).Value = SoM
        Cells(rw, 17).NumberFormat = "m/d/yyyy"
        rw = rw + 1
    End If
    SoM = SoM + 1
Wend
2
votes

I made a user defined function that works with any date range, then show how it could be applied to this example with a few formulas. This would account for year to year transitions.

enter image description here

Function DAYOFWEEKFREQUENCY(ByVal dayOfWeekType As String, ByVal startDate As String, ByVal endDate As String) As Long
    Dim myStartDate As Date
        myStartDate = CDate(startDate)

    Dim myEndDate As Date
        myEndDate = CDate(endDate)

    Dim includeStartDate As Long
        includeStartDate = 1

    Dim daysBetweenDatesInclusive As Long
        daysBetweenDatesInclusive = Application.WorksheetFunction.Days(endDate, startDate) + includeStartDate

    Dim vbStartDay As Long
        vbStartDay = Weekday(startDate)

    Dim dateCheckedIncremented As Date
        dateCheckedIncremented = myStartDate

    For dayCounter = 1 To daysBetweenDatesInclusive
        If Weekday(dateIncrementedChecked) = dayOfWeekType Then
            DAYOFWEEKFREQUENCY = DAYOFWEEKFREQUENCY + 1
        End If
        dateIncrementedChecked = DateAdd("d", 1, dateIncrementedChecked)
    Next
End Function