Build rotating schedule and skip last Sunday of every month

[jeffreybrown]

I posted a thread some time back asking to skip the first Wed of every month. Now I'm looking to skip the last Sunday of every month.

The offered solution was =IF(L2=1,"Singing",IF(L2="","",INDEX(SongLeadersWed,IF(MOD(COUNTIF(L$1:$L1,">1"),'DV List'!$J$1)=0,1,MOD(COUNTIF(L$1:$L1,">1"),'DV List'!$J$1)+1))))

I would like to apply the formula to column G.

In months, Mar, Jun, Sep, and Dec there are five Sunday's, so the "Singing" would be on the fifth Sunday versus the fourth. The order in column G should come from the list in K4:K11.

Any thoughts?

[Bo_Ry]

Please try at G2 and drag down

=IF(A2=INT((EOMONTH(A2,0)-1)/7)*7+1,"Singing",INDEX($K$4:$K$11,MOD(ROWS(G$2:G2)-1-COUNTIF(G$1:G1,"Singing"),8)+1))

[jeffreybrown]

Thank you Bo. This works great.