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

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

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

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

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

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

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

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
------------------------------------------------------------------------
Flintstone's Profile: http://www.excelforum.com/member.php...o&userid=15310
View this thread: http://www.excelforum.com/showthread...hreadid=482360

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

Thank you Dave Peterson from 13 years ago. Where does time go?

On Friday, November 4, 2005 at 5:29:59 PM UTC-8, 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

--

Dave Peterson

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

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" wrote in message
...
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

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

On Fri, 04 Nov 2005 14:35:36 -0800, 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

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