Results 1 to 15 of 15

20111124, 17:55 #1
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Finding 1st and 3rd Fridays of each month
Hi all....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.

20111124, 20:06 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 7,024
 Thanks
 237
 Thanked 967 Times in 883 Posts
Attached is a worksheet that will calculate the 1st & 3rd Fridays in table form by simply changing the year. You could then reference the table from your worksheet. It may be possible to do a single formula but I can't come up with it and it is going to be complicated.

20111124, 20:45 #3
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,166
 Thanks
 16
 Thanked 41 Times in 40 Posts
Try this. EDATE comes from the Analysis Tookpak.
Fill down PAIRS of rows.Last edited by kweaver; 20111124 at 20:45. Reason: add'l info

20111125, 04:22 #4
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 7,024
 Thanks
 237
 Thanked 967 Times in 883 Posts

20111125, 06:34 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Crossposted at http://eileenslounge.com/viewtopic.php?f=27&t=8681
Steve

20111125, 08:31 #6
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 7,024
 Thanks
 237
 Thanked 967 Times in 883 Posts
Steve,
Thanks for the link to the cross post. I was able to get the formula to work with a change to the parenthesis.
Attached is a worksheet in which the formula is used and is dragable as long as you drag 2 rows at a time for 1st and 3rd Fridays.
I hope this is what the OP was looking for? :cheers:

20111125, 11:10 #7
Are you referring to the generic formula that I posted to that crossposted link? If so, I am not sure what you mean when you said you had to change the parenthesis... the formula I posted was tested and works fine with the parentheses that I used (it had to because it was derived from the generic formula for finding the Nth suchandsuch day of the month that I posted there). Namely, this formula...
=DATE(YEAR(A1),MONTH(A1),1+7*1)WEEKDAY(DATE(YEAR(A1),MONTH(A1),86))
Also, referring to the worksheet you attached, a much simpler formula for E5 would be this...
=E4+14
since the 3rd Friday of the month is always 14 days away from the 1st Friday of the month which the first formula above calculates and which could be used in E4 in place of the formula you posted.

20111125, 11:38 #8
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 7,024
 Thanks
 237
 Thanked 967 Times in 883 Posts
Rick,
Welcome to the lounge as a poster!
This is the one I was refering to: =DATE(YEAR(A1),MONTH(A1),8)WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))
I changed it to: =DATE(YEAR($C4),MONTH($C4),(8WEEKDAY(DATE(YEAR($C4),MONTH($C4),2))))
Did I miss something? Of course $C4 was substituted for A1.
You are correct the +14 would be easier and more efficient I just got caught up in the formula!

20111125, 11:57 #9
Thanks!
Both formulas work, so I am not sure why you felt you had to change my formula to your version of it... given they both use the same number of function calls (actually, the identical function calls), I don't see where changing it gained anything (actually, your formula is 2 characters longer than the one I posted for those who count such things). Or did my version not work on your system for some reason?Last edited by rickweb; 20111125 at 12:01.

20111125, 12:16 #10
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 7,024
 Thanks
 237
 Thanked 967 Times in 883 Posts
Rick,
Before I changed it I was getting 1900 as the year although the month & day were correct as I remember.
Well, I just went back and tried it again and it worked just fine, sorry for the confusion.

20111125, 12:22 #11
That may have been because I used A1 as the cell reference and A1 was blank. You probably reacted to that and changed both the parentheses and cell references at the same time when trying to fix the problem you thought you saw.
No problem... I was just trying to understand why you did what you posted you did.

20111201, 16:10 #12
 Join Date
 Dec 2009
 Location
 Syracuse, NY USA
 Posts
 48
 Thanks
 0
 Thanked 2 Times in 2 Posts
Excel calculate 1st friday of month and 3rd Friday of month
Here are the formulas for computing the 1st and 3rd Friday of a month based upon a specified date in another cell.
for 1st Friday of Month (change both cell references B2 to source date)
=EOMONTH(B2,1)+1+MOD(14(WEEKDAY(EOMONTH(B2,1)+1)+1),7)
for 3rd Friday of Month
=above cell +14
or =EOMONTH(B2,1)+15+MOD(14(WEEKDAY(EOMONTH(B2,1)+1)+1),7)
This requires the Analysis Tool Pack (in Excel 2003). In Excel 2003 use menu Tools, AddIns, Analysis Tool pack. Don’t know about later versions of Excel
Attachements:
1st3rdFriday.xls: sample spreadsheet using selected days in December
1st3rdFriday.doc: above information, breakdown of functions

20111210, 02:45 #13
Whoa! Is the OP still tuned in!? And I thought MY formula was complex.
We just need to determine the first Friday. Every other Friday is simply +14 days. I determine the first Friday stepbystep, with the formula in D5 determining the first (or third) Friday. The formula in G5 does the same thing in one step, but is much harder to follow.
The series will include a 5th Friday every few months, but those can just be ignored if I get the OP's intent. I used conditional formating to highlight the 5th Friday of any month.
I test for WEEKDAY(date,3)>4 to determine how many days to add to the Start Date to get the 1st Friday. I use return type "3" because it returns 0 thru 6, rather than 17, and makes the math a tiny bit more intuitive. The MOD function can be used for this too, as shown in the other examples, but it adds to complexity IMHO.
So, OP, you have just seen several ways to skin the cat. Feedback? ~RonR
OOPS! My calc has errors. The 5th Friday causes the following dates to be the 2nd and 4th Fridays, not the 1st and 3rd. I'm working on it. ~RonRLast edited by ruosChalet; 20111210 at 03:18. Reason: Correction

20111210, 03:50 #14OOPS! My calc has errors. The 5th Friday causes the following dates to be the 2nd and 4th Fridays, not the 1st and 3rd. I'm working on it. ~RonR
That is, dateabove + 14 becomes dateabove + 14  7 * (0, or 1 if dateabove is a 5th Friday).
Of course, the complexity just went up. The test for 5th Friday could also be used to delete (skip) the 5th Friday in the list, but at the cost of even more complexity. ~RonR.

20111211, 17:39 #15
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Hi loungers.....sorry to be so tardy in catching up with things and thanking you for your help....SDCKAPR has mentioned that I crossposted this on another forum (http://eileenslounge.com/viewtopic.php?f=27&t=8681)....I also got some answers from folks in that forum....aside from the most recent suggestion that came in yesterday from ruosChalet, I have tried the various suggestions, and so far, the one provided by Hans at eileenslounge.com has worked easily and is most reliable.....thank you, again to anyone who gave my some feedback and answers.