Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
David
 
Posts: n/a
Default 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   Report Post  
Dave Peterson
 
Posts: n/a
Default 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   Report Post  
bpeltzer
 
Posts: n/a
Default 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   Report Post  
Biff
 
Posts: n/a
Default 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" 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



  #5   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default 1st and 3rd Thursday formula

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


  #6   Report Post  
David
 
Posts: n/a
Default 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   Report Post  
David
 
Posts: n/a
Default 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   Report Post  
David
 
Posts: n/a
Default 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   Report Post  
David
 
Posts: n/a
Default 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   Report Post  
David
 
Posts: n/a
Default 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   Report Post  
David
 
Posts: n/a
Default 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   Report Post  
Flintstone
 
Posts: n/a
Default 1st and 3rd Thursday formula


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

  #13   Report Post  
David
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 1st and 3rd Thursday formula

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 09:41 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"