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