Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 23
Default 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.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,118
Default 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.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,393
Default 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.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default 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.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 136
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 23
Default 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.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 23
Default 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.




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 23
Default 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.

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 174
Default 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


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


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I extract a month from Excel date? lee Excel Discussion (Misc queries) 6 October 26th 06 02:43 AM
Current date formula based on month Renz09 Excel Discussion (Misc queries) 2 May 5th 06 07:04 AM
Month to date sales - reset in new month??? [email protected] Excel Worksheet Functions 2 November 26th 05 09:18 PM
NETWORKDAYS - Multiple Date Selection Annabelle Excel Discussion (Misc queries) 3 October 4th 05 07:04 PM
Month Year Date Format Jamie Excel Worksheet Functions 2 February 7th 05 07:43 PM


All times are GMT +1. The time now is 12:08 PM.

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"