Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I extract a month from Excel date? | Excel Discussion (Misc queries) | |||
Current date formula based on month | Excel Discussion (Misc queries) | |||
Month to date sales - reset in new month??? | Excel Worksheet Functions | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
Month Year Date Format | Excel Worksheet Functions |