1st and 3rd Thursday formula

  • Thread starter Thread starter David
  • Start date Start date
D

David

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
 
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)
 
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
 
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
 
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
Click to expand...

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
 
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)
Click to expand...

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.
 
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!
Click to expand...

It should default to first Thursday of current month.
 
Dave Peterson wrote
Me, personally--I'd use some helper cells instead of creating a giant
formula.
Click to expand...

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

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.
 
Dave Peterson wrote
=DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*3
-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5)))
Click to expand...

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.
 
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
 
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
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I get a formula to calculate the third Thursday of the mont 7
counting dates 7
IPad remote 10
The closest "specific day" to a specific date. 5
Formula for adding a day to a particular date. 1
Extending existing coding to include new parameters 19
Reference the higest value 2
Automating consecutive dates matching dates of the week in columns 4

Back
Top