+ Reply to Thread
Results 1 to 17 of 17

1st and 3rd Thursday formula

  1. #1
    David
    Guest

    1st and 3rd Thursday formula

    Need a formula to display date to satisfy these requirements.
    If today is beyond first Thursday of the month: 3rd Thursday
    If today is beyond third Thursday of the month: 1st Thursday of next month

    --
    David

  2. #2
    Dave Peterson
    Guest

    Re: 1st and 3rd Thursday formula

    Bob Phillips posted this:

    Generic formula

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

    Nth is the instance, 1st,.2nd etc, DoW is the serial number of the Day,
    Sun=1, Mon=2, etc.

    ======
    Me, personally--I'd use some helper cells instead of creating a giant formula.

    First Thursday of this month:
    =DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*1)
    -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5))

    Third Thursday of this month:
    =DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*3
    -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5)))

    First Thursday of next month:
    =DATE(YEAR(TODAY()),MONTH(TODAY())+1,1+7*1)
    -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,8-5))

    With those values in (say) A1, B1, C1, I'd use this formula:

    =if(today()>b1,c1,if(today()>a1,b1,a1))

    (good gawd--if you want a single cell formula:

    =IF(TODAY()>(DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*3
    -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5)))),
    (DATE(YEAR(TODAY()),MONTH(TODAY())+1,1+7*1)
    -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,8-5))),
    IF(TODAY()>(DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*1)
    -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5))),
    (DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*3
    -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5)))),
    (DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*1)
    -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5)))))

    (just copy and paste any of those multilined formulas into the formula bar)




    David wrote:
    >
    > Need a formula to display date to satisfy these requirements.
    > If today is beyond first Thursday of the month: 3rd Thursday
    > If today is beyond third Thursday of the month: 1st Thursday of next month
    >
    > --
    > David


    --

    Dave Peterson

  3. #3
    bpeltzer
    Guest

    RE: 1st and 3rd Thursday formula

    I can get there with a lookup table and a helper column.
    The table converts the weekday of the last day of last month to the day of
    this month's first Thursday. The first row: 1,2,3,4,5,6,7. The second row:
    4,3,2,1,7,6,5. Put that in Sheet2!A1:G2.
    In A10:
    =DATE(YEAR(TODAY()),MONTH(TODAY()),HLOOKUP(WEEKDAY(TODAY()-DAY(TODAY())),Sheet2!$A$1:$G$2,2,FALSE))
    In B10:
    =IF(TODAY()<=A10,A10,IF(TODAY()<=A10+14,A10+14,IF(DAY(A10+28)>28,A10+28,A10+35)))
    The first formula figures out the date of the first Thursday of this month.
    The second does the calculation you described: if we're on or before the
    first Thursday of this month, return the first Thursday. Otherwise, if we're
    on or before the third Thursday, return the third Thursday. Otherwise,
    advance to the first Thursday of next month.
    --Bruce


    "David" wrote:

    > Need a formula to display date to satisfy these requirements.
    > If today is beyond first Thursday of the month: 3rd Thursday
    > If today is beyond third Thursday of the month: 1st Thursday of next month
    >
    > --
    > David
    >


  4. #4
    Biff
    Guest

    Re: 1st and 3rd Thursday formula

    Hi!

    What do want to do if today is less than or equal to the first Thursday of
    the month?

    The formula to do this would be very long!

    Biff

    "David" <[email protected]> wrote in message
    news:[email protected]...
    > Need a formula to display date to satisfy these requirements.
    > If today is beyond first Thursday of the month: 3rd Thursday
    > If today is beyond third Thursday of the month: 1st Thursday of next month
    >
    > --
    > David




  5. #5
    Ron Rosenfeld
    Guest

    Re: 1st and 3rd Thursday formula

    On Fri, 04 Nov 2005 14:35:36 -0800, David <[email protected]> wrote:

    >Need a formula to display date to satisfy these requirements.
    >If today is beyond first Thursday of the month: 3rd Thursday
    >If today is beyond third Thursday of the month: 1st Thursday of next month


    Complicated, but doable:

    =IF(A1<=(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+3)),
    A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+3),IF(AND(
    A1>(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+3)),A1<=(
    A1-DAY(A1)+14+8-WEEKDAY(A1-DAY(A1)+3))),A1-DAY(
    A1)+14+8-WEEKDAY(A1-DAY(A1)+3),A1-DAY(A1)+40-DAY(
    A1-DAY(A1)+32)-WEEKDAY(A1-DAY(A1)+35-DAY(A1-DAY(A1)+32))))


    --ron

  6. #6
    David
    Guest

    Re: 1st and 3rd Thursday formula

    Dave Peterson wrote

    > Bob Phillips posted this:
    >
    > Generic formula
    >
    > =DATE(YEAR(A1),MONTH(A1),1+7*Nth)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-DoW
    > ))
    >
    > Nth is the instance, 1st,.2nd etc, DoW is the serial number of the
    > Day, Sun=1, Mon=2, etc.
    >
    > ======
    > Me, personally--I'd use some helper cells instead of creating a giant
    > formula.
    >
    > First Thursday of this month:
    > =DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*1)
    > -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5))
    >
    > Third Thursday of this month:
    > =DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*3
    > -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5)))
    >
    > First Thursday of next month:
    > =DATE(YEAR(TODAY()),MONTH(TODAY())+1,1+7*1)
    > -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,8-5))
    >
    > With those values in (say) A1, B1, C1, I'd use this formula:
    >
    > =if(today()>b1,c1,if(today()>a1,b1,a1))
    >
    > (good gawd--if you want a single cell formula:
    >
    > =IF(TODAY()>(DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*3
    > -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5)))),
    > (DATE(YEAR(TODAY()),MONTH(TODAY())+1,1+7*1)
    > -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,8-5))),
    > IF(TODAY()>(DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*1)
    > -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5))),
    > (DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*3
    > -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5)))),
    > (DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*1)
    > -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5)))))
    >
    > (just copy and paste any of those multilined formulas into the formula
    > bar)
    >
    >
    >
    >
    > David wrote:
    >>
    >> Need a formula to display date to satisfy these requirements.
    >> If today is beyond first Thursday of the month: 3rd Thursday
    >> If today is beyond third Thursday of the month: 1st Thursday of next
    >> month
    >>
    >> --
    >> David

    >


    Good gawd is right!! Especially since I want to surround the result with
    some text!! I think I'll just type in what I need twice a month.

    Thanks to all who took the time and trouble to present solutions.

    --
    David

  7. #7
    David
    Guest

    Re: 1st and 3rd Thursday formula

    Biff wrote

    > What do want to do if today is less than or equal to the first
    > Thursday of the month?
    >
    > The formula to do this would be very long!


    It should default to first Thursday of current month.

    --
    David

  8. #8
    David
    Guest

    Re: 1st and 3rd Thursday formula

    Dave Peterson wrote

    > Me, personally--I'd use some helper cells instead of creating a giant
    > formula.


    Hmm... Curiously, after experimenting by adjusting computer clock/calendar,
    I couldn't advance result past Dec 1st.

    --
    David

  9. #9
    David
    Guest

    Re: 1st and 3rd Thursday formula

    David wrote

    > Hmm... Curiously, after experimenting by adjusting computer
    > clock/calendar, I couldn't advance result past Dec 1st.


    Correction. Tests will go as far as Dec 15. If I set date to Dec 15 or
    later, result reverts to Dec 1. Doesn't want to go past Dec 15 or into Jan,
    2006.

    --
    David

  10. #10
    David
    Guest

    Re: 1st and 3rd Thursday formula

    Dave Peterson wrote

    > With those values in (say) A1, B1, C1, I'd use this formula:


    Setting system date to Dec 4
    A1: 11/3/2005
    B1: 12/15/2005
    C1: 12/1/2005


    --
    David

  11. #11
    David
    Guest

    Re: 1st and 3rd Thursday formula

    Dave Peterson wrote

    > =DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*3
    > -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5)))


    Ok, changed other two formulas to match this syntax, i.e., placement of
    parentheses, and it works.

    p.s. Sorry about not changing date back to today before sending prior post.

    --
    David

  12. #12
    Forum Contributor
    Join Date
    10-14-2004
    Location
    San Diego, CA
    Posts
    213
    Hi David:

    This is pretty generic, but it works.

    1. Go through the calendar and write down all the Thursday’s that meet your criteria.

    2. Place them in order and in a column somewhere off the visible area of the work sheet such as column AA.

    =IF($A$1<=AA1,AA1,"")

    3. Place this formula in AB1 and copy it down to match the range of dates in the AA column, cell $A$1 is whatever cell you keep today’s date in.

    =MIN(AB1:AB27)

    4. Place this formula in whatever cell you choose to see the results and format that cell to date.



    Matt

  13. #13
    David
    Guest

    Re: 1st and 3rd Thursday formula

    Flintstone wrote

    >
    > Hi David:
    >
    > This is pretty generic, but it works.
    >
    > 1. Go through the calendar and write down all the Thursday’s that meet
    > your criteria.
    >
    > 2. Place them in order and in a column somewhere off the visible area
    > of the work sheet such as column AA.
    >
    > =IF($A$1<=AA1,AA1,"")
    >
    > 3. Place this formula in AB1 and copy it down to match the range of
    > dates in the AA column, cell $A$1 is whatever cell you keep today’s
    > date in.
    >
    > =MIN(AB1:AB27)
    >
    > 4. Place this formula in whatever cell you choose to see the results
    > and format that cell to date.
    >
    >
    >
    > Matt
    >
    >


    Thanks for jumping in. I want this to be perpetual and maintenance free, so
    I decided to go with Dave Petersen's 'helper cell' formulas.

    --
    David

  14. #14
    Registered User
    Join Date
    02-25-2017
    Location
    San Diego
    MS-Off Ver
    Excel 2013
    Posts
    1

    Re: 1st and 3rd Thursday formula

    So how would I change this formula to reflect the 2nd and 4th Thursdays?

    =IF(B8<=(B8-DAY(B8)+8-WEEKDAY(B8-DAY(B8)+3)),B8-DAY(B8)+8-WEEKDAY(B8-DAY(B8)+3),IF(AND(B8>(B8-DAY(B8)+8-WEEKDAY(B8-DAY(B8)+3)),B8<=(B8-DAY(B8)+14+8-WEEKDAY(B8-DAY(B8)+3))),B8-DAY(B8)+14+8-WEEKDAY(B8-DAY(B8)+3),B8-DAY(B8)+40-DAY(B8-DAY(B8)+32)-WEEKDAY(B8-DAY(B8)+35-DAY(B8-DAY(B8)+32))))

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: 1st and 3rd Thursday formula

    gtluv01 welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  16. #16
    Registered User
    Join Date
    01-02-2018
    Location
    Cincinnati, OH
    MS-Off Ver
    MS Office 365 Pro Plus
    Posts
    1

    Re: 1st and 3rd Thursday formula

    I was able to get the formula to work, How do I make sure as time goes on that my dates won't get messed up? Is there a place in the formula that I can put in the Year and the month? I am worried that because it is based on today's date that the dates will move as the months move.


    This is the formula I ended up using
    =DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*1) -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5))

    for next month I used
    =DATE(YEAR(TODAY()),MONTH(TODAY())+1,1+7*1) -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,8-5))

  17. #17
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: 1st and 3rd Thursday formula

    Hello hjbinn01,

    Please start your own thread with your specific query - you can link to this thread if it's relevant
    Audere est facere

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1