How do I get the date for the 2nd friday of each month?
 

I'm trying to come up with a formula that can reference a cell containing
date data and tell me what the 2nd friday of that month would be.

I.E. Cell Value = 10/02/2006; formula would say Friday, October 13, 2006.

How do I get the date for the 2nd friday of each month?
 

Here's one way:

With
A1: (a date)

The second Friday of the month containing the date in A1
B1: =A1-DAY(A1)+CHOOSE(WEEKDAY(A1-DAY(A1),2),11,10,9,8,14,13,12)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Dustin" wrote:

I'm trying to come up with a formula that can reference a cell containing
date data and tell me what the 2nd friday of that month would be.

I.E. Cell Value = 10/02/2006; formula would say Friday, October 13, 2006.

How do I get the date for the 2nd friday of each month?
 

With Oct 2 2006 in A1 (displayed in whatever format you use)
=DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YE AR(A1),MONTH(A1),1)),5,4,3,2,1,0,6)+7
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dustin" wrote in message
...
I'm trying to come up with a formula that can reference a cell containing
date data and tell me what the 2nd friday of that month would be.

I.E. Cell Value = 10/02/2006; formula would say Friday, October 13, 2006.

How do I get the date for the 2nd friday of each month?
 

Try this:
=A1+13-WEEKDAY(A1)


"Dustin" wrote:

I'm trying to come up with a formula that can reference a cell containing
date data and tell me what the 2nd friday of that month would be.

I.E. Cell Value = 10/02/2006; formula would say Friday, October 13, 2006.

How do I get the date for the 2nd friday of each month?
 

=A1-DAY(A1)+14-MOD(A1-DAY(A1)+1,7)

Regards,
Bernd

How do I get the date for the 2nd friday of each month?
 

Wow! Thanks Ron!!!

"Ron Coderre" wrote:

Here's one way:

With
A1: (a date)

The second Friday of the month containing the date in A1
B1: =A1-DAY(A1)+CHOOSE(WEEKDAY(A1-DAY(A1),2),11,10,9,8,14,13,12)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Dustin" wrote:

I'm trying to come up with a formula that can reference a cell containing
date data and tell me what the 2nd friday of that month would be.

I.E. Cell Value = 10/02/2006; formula would say Friday, October 13, 2006.

How do I get the date for the 2nd friday of each month?
 

Thanks Bernard!


"Bernard Liengme" wrote:

With Oct 2 2006 in A1 (displayed in whatever format you use)
=DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YE AR(A1),MONTH(A1),1)),5,4,3,2,1,0,6)+7
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dustin" wrote in message
...
I'm trying to come up with a formula that can reference a cell containing
date data and tell me what the 2nd friday of that month would be.

I.E. Cell Value = 10/02/2006; formula would say Friday, October 13, 2006.

How do I get the date for the 2nd friday of each month?
 

Thanks!

"Teethless mama" wrote:

Try this:
=A1+13-WEEKDAY(A1)


"Dustin" wrote:

I'm trying to come up with a formula that can reference a cell containing
date data and tell me what the 2nd friday of that month would be.

I.E. Cell Value = 10/02/2006; formula would say Friday, October 13, 2006.

How do I get the date for the 2nd friday of each month?
 

Hi Bernd,

this works OK with Excel's default date system but would fail if you use
1904 date system, better to use a formula which would work with either

=A1-DAY(A1)+15-WEEKDAY(A1-DAY(A1)+2)

" wrote:

=A1-DAY(A1)+14-MOD(A1-DAY(A1)+1,7)

Regards,
Bernd