1st and 3rd Fridays of each month

[dmcnab]

Hi loungers....I have been all over the place looking for a set of formulae that will display the 1st and 3rd Friday of each month....I attach a small sample workbook...you will see Jan 1, 2012 in D4.....the corresponding day is in C4...I am looking for formula for D5 (that I can fill down) that will show the 1st and 3rd Friday of each month...so that when I change D4 to be Jan 1, 2013 or Jan 1, 2014 the dates below will change to the 1st and 3rd Friday of each month for the year in question.....I have found or devised formula that will show each Friday, but that won't work in the real life workbook b/c some months have more than 4 Fridays etc.....any ideas? Thanks in advance.

[HansV]

I think you'll need one formula in D5 to get the first Friday of the year, then a different formula in D6 to get the next one in the series. The formula in D6 can be filled down.

In D5: =D4+MOD(6-WEEKDAY(D4),7)

In D6: =D5+14+7*AND(DAY(D5)>10,DAY(D5+14)>10)

[Rick Rothstein]

I think you'll need one formula in D5 to get the first Friday of the year, then a different formula in D6 to get the next one in the series...

You don't need to start at the beginning of the year. Here is a generic formula to find the Nth such and such day of a given month...

=DATE(Y,M,1+7*Nth)-WEEKDAY(DATE(Y,M,8-DoW))

where Y is the year, M is the month, Nth is the number you want 1st, 2nd, 3rd etc and where DoW stands for day of the week with Sunday being 1, Monday being 2 and so on. So, if you want the 1st Friday in the month for the date in A1, substituting into the generic formula above gives...

=DATE(YEAR(A1),MONTH(A1),1+7*1)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-6))

which simplifies to this...

=DATE(YEAR(A1),MONTH(A1),8)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))

[sdckapr]

Cross-posted at http://windowssecrets.com/forums/showth ... each-month

Steve

[dmcnab]

Hi loungers.....sorry to be so tardy in catching up with things and thanking you for your help....SDCKAPR has mentioned that I cross-posted this on another forum....I also got some answers from folks in that forum.....I have tried the various suggestions, and so far, the one provided here by Hans has worked easily and is most reliable.....thank you, again to anyone who gave my some feedback and answers.