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

[Dustin]

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.

 

[Ron Coderre]

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

 

[Bernard Liengme]

With Oct 2 2006 in A1 (displayed in whatever format you use)
=DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),5,4,3,2,1,0,6)+7
best wishes

 

[Teethless mama]

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

 

[bplumhoff]

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

Regards,
Bernd

 

[Dustin]

Wow! Thanks Ron!!!

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]

Thanks Bernard!

With Oct 2 2006 in A1 (displayed in whatever format you use)
=DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),5,4,3,2,1,0,6)+7
best wishes

 

[Dustin]

Thanks!

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

 

[daddylonglegs]

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)