Results 1 to 15 of 15
  1. #1
    4 Star Lounger
    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.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,378
    Thanks
    207
    Thanked 829 Times in 762 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.
    Attached Images Attached Images
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,100
    Thanks
    13
    Thanked 37 Times in 36 Posts
    Try this. EDATE comes from the Analysis Tookpak.

    Fill down PAIRS of rows.
    Attached Files Attached Files
    Last edited by kweaver; 2011-11-24 at 21:45. Reason: add'l info

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,378
    Thanks
    207
    Thanked 829 Times in 762 Posts
    Quote Originally Posted by RetiredGeek View Post
    It may be possible to do a single formula but I can't come up with it and it is going to be complicated.
    K,

    Nice job! And you proved my point as well.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,378
    Thanks
    207
    Thanked 829 Times in 762 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:
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  7. #7
    New Lounger rickweb's Avatar
    Join Date
    Nov 2011
    Location
    New Jersey, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    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?
    Are you referring to the generic formula that I posted to that cross-posted 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 such-and-such 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),8-6))

    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.

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,378
    Thanks
    207
    Thanked 829 Times in 762 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),(8-WEEKDAY(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!
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  9. #9
    New Lounger rickweb's Avatar
    Join Date
    Nov 2011
    Location
    New Jersey, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Rick,

    Welcome to the lounge as a poster!
    Thanks!

    Quote Originally Posted by RetiredGeek View Post
    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),(8-WEEKDAY(DATE(YEAR($C4),MONTH($C4),2))))
    Did I miss something? Of course $C4 was substituted for A1.
    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; 2011-11-25 at 13:01.

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,378
    Thanks
    207
    Thanked 829 Times in 762 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.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  11. #11
    New Lounger rickweb's Avatar
    Join Date
    Nov 2011
    Location
    New Jersey, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Before I changed it I was getting 1900 as the year although the month & day were correct as I remember.
    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.

    Quote Originally Posted by RetiredGeek View Post
    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.
    No problem... I was just trying to understand why you did what you posted you did.

  12. #12
    Lounger
    Join Date
    Dec 2009
    Location
    Syracuse, NY USA
    Posts
    46
    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, Add-Ins, 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
    Attached Files Attached Files

  13. #13
    Lounger ruosChalet's Avatar
    Join Date
    Dec 2009
    Location
    Seattle WA
    Posts
    28
    Thanks
    26
    Thanked 3 Times in 3 Posts
    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 step-by-step, 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 1-7, 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. ~RonR
    Attached Files Attached Files
    Last edited by ruosChalet; 2011-12-10 at 04:18. Reason: Correction

  14. #14
    Lounger ruosChalet's Avatar
    Join Date
    Dec 2009
    Location
    Seattle WA
    Posts
    28
    Thanks
    26
    Thanked 3 Times in 3 Posts
    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. ~RonR
    Okay, here is the fix. In cells D6 and after, I've added a test for the previous Friday being a "5th." Then, instead of adding 14 days, only 7 are added (14-7).

    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.
    Attached Files Attached Files

  15. #15
    4 Star Lounger
    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 cross-posted 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •