Counting Thursdays to find a last date.

[AdSpread]

I am computing an end date for the weekly publication of an advertisement. The inputs are the start date and the number of total insertions, while which Thursdays to insert the ad is based on a binary pattern like 00101 which would mean the third and fifth Thursday of the month - every week is 11111. From a given start date (always a Thursday) when is the last Thursday on which there will be an insertion, for any number of insertions.
This problem would be relatively easy as a macro but i would like to make it happen inside a cell as a single function. I have tried many things, even making a lookup table that had a row for every one of the 31 possibilities and a column for every week for a couple of decades! But that's pretty clumsy (though it works).
Suggestions eagerly received.

 

[Rick Rothstein]

The following array-entered** formula assumes cell A1 contains the date for the first Thursday of the month, that the pattern is in a cell formatted as Text, that the pattern is always 5 digits long and if the fifth digit in the pattern for a month with only 4 Thursdays in it is a one, that the 4th digit is also a one (this means the formula would not handle be able to handle a pattern like 10101 correctly in a month with only 4 Thursdays without becoming much longer). With those conditions, here is the formula...

=A1+7*(MAX(IF(MID(A2,ROW(1:5),1)="1",ROW(1:5)))-1-(RIGHT(A2)="1")*(MONTH(A2)<>MONTH(A2+28)))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[Rick Rothstein]

The following array-entered** formula assumes cell A1 contains the date for the first Thursday of the month, that the pattern is in a cell formatted as Text, that the pattern is always 5 digits long and if the fifth digit in the pattern for a month with only 4 Thursdays in it is a one, that the 4th digit is also a one (this means the formula would not handle be able to handle a pattern like 10101 correctly in a month with only 4 Thursdays without becoming much longer). With those conditions, here is the formula...

=A1+7*(MAX(IF(MID(A2,ROW(1:5),1)="1",ROW(1:5)))-1-(RIGHT(A2)="1")*(MONTH(A2)<>MONTH(A2+28)))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

Forget the above formula as I believe the one below will correctly do what you want. This part of my assumptions still apply...

The following array-entered** formula assumes cell A1 contains the date for the first Thursday of the month, that the pattern is in a cell formatted as Text, that the pattern is always 5 digits long.

However, if the pattern has a one in the fifth digit position and the month does not have five Thursdays in it, then that fifth digit is ignored for that month and then next right-most one is used. So, for a date of 5/2/2019 (the first Thursday in that month for that year) in cell A1, then the formula will return 5/30/2019 (after you format the cell the formula is in as a date); however, if cell A1 has 6/6/2019 in it (the first Thursday in that month for that year), then the formula will return 6/20/2019 because the 3rd Thursday is the last Thursday the pattern can apply to (as there is no 5th Thursday).

Okay, with all that said, here is the array-entered** formula that produces these results...

=A1+7*(MAX(IF(MID(A2,ROW(INDIRECT("1:"&4+(RIGHT(A2)="1")*(MONTH(A1)=MONTH(A1+28)))),1)="1",ROW(INDIRECT("1:"&4+(RIGHT(A2)="1")*(MONTH(A1)=MONTH(A1+28))))))-1)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[Eric W]

This non-array formula I also think works:

=IFERROR(A1+7*MAX(IF(MID(A2,{1,2,3,4,5},1)="1",IF(A1+{0,7,14,21,28}>EOMONTH(A1,0),"A",{0,1,2,3,4}))),"That date is beyond the end of the month")

Except that in case of a 1 in the fifth position and there's not 5 Thursdays in the month, it just generates an error message.

And if you don't care if you go over to the next month, the formula is just:

=A1+MAX(IF(MID(A2,{1,2,3,4,5},1)="1",{0,7,14,21,28}))

 

[Tetra201]

... The inputs are the start date and the number of total insertions, while which Thursdays to insert the ad is based on a binary pattern like 00101...
This problem would be relatively easy as a macro but i would like to make it happen inside a cell as a single function...

Here is a UDF (User-Defined Function) for you to try.

Usage examples:
=EndDate("2019-1-1",4,"10101")
=EndDate(A1,B1,C1), where cells A1, B1, and C1 contain the start date, the total number of insertions, and the monthly pattern. The start date doesn't have to be a Thursday.

Caveats:
- the UDF would work in Excel 2010 and above;
- the total number of insertion cannot be less than 1;
- the "0000000" monthly pattern is invalid;
- if a month has four Thursdays only, the fifth symbol in the monthly pattern is disregarded.

Function EndDate(StartDate As Date, NumAds As Long, Pattern As String) As Date
    Dim ThuDate As Long, StartNo As Long, NumThu As Long, i As Long, j As Long
    ThuDate = Application.WorkDay_Intl(StartDate - 1, 1, "1110111")
    StartNo = Evaluate("(" & ThuDate & "-WORKDAY.INTL(EOMONTH(" & ThuDate & ",-1)+1,-1,""1110111""))/7")
    i = 0
    Do
        NumThu = Evaluate("NETWORKDAYS.INTL(EOMONTH(" & ThuDate & ",-1)+1,EOMONTH(" & ThuDate & ",0),""1110111"")")
        For j = StartNo To NumThu
            If Mid(Pattern, j, 1) = "1" Then i = i + 1
            If i = NumAds Then Exit Do
            ThuDate = ThuDate + 7
        Next j
        StartNo = 1
    Loop Until i > NumAds
    EndDate = ThuDate
End Function