Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Second Friday
Hello Everyone,
If I start with a specific date (01-08-07) how do I get to the second friday date of the month of (01-19-07) using Excel? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Second Friday
If this date is in A1 use:
=DATE(YEAR(A1),MONTH(A1),1)+13-MOD(DATE(YEAR(A1),MONTH(A1),1),7) HTH Kostis Vezerides On Jan 26, 4:42 pm, "Deb" wrote: Hello Everyone, If I start with a specific date (01-08-07) how do I get to the second friday date of the month of (01-19-07) using Excel? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Second Friday
Try this
=DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(A1),5,4 ,3,2,1,0,6)+7 best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Deb" wrote in message ups.com... Hello Everyone, If I start with a specific date (01-08-07) how do I get to the second friday date of the month of (01-19-07) using Excel? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Second Friday
Try this:
With A1: (reference date) B1: (the day to find....Sun: 1, Mon: 2, ....Sat: 7) C1: (the occurance number to find) To find the nth occurance of the DayNum in B1 that is AFTER the ref date in A1 D1: =A1-WEEKDAY(A1)+(7*(C1))+B1 Example: A1: Jan-08-2007 B1: 6 (meaning Friday) C1: 2 (find the 2nd occurance) D1 returns Jan-19-2007 However.....if you want the nth occurance of the day that is in the MONTH including the date in A1.... Then D1: =A1-DAY(A1)-WEEKDAY(A1-DAY(A1))+(7*(C1-1))+B1 Example: A1: Jan-08-2007 B1: 6 (meaning Friday) C1: 2 (find the 2nd occurance) D1 returns Jan-12-2007 Does that help? *********** Regards, Ron XL2002, WinXP "Deb" wrote: Hello Everyone, If I start with a specific date (01-08-07) how do I get to the second friday date of the month of (01-19-07) using Excel? Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Second Friday
Typo in the first formula (sorry)
It should be: To find the nth occurance of the DayNum in B1 that is AFTER the ref date in A1 D1: =A1-WEEKDAY(A1)+(7*(C1-1))+B1 *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try this: With A1: (reference date) B1: (the day to find....Sun: 1, Mon: 2, ....Sat: 7) C1: (the occurance number to find) To find the nth occurance of the DayNum in B1 that is AFTER the ref date in A1 D1: =A1-WEEKDAY(A1)+(7*(C1))+B1 Example: A1: Jan-08-2007 B1: 6 (meaning Friday) C1: 2 (find the 2nd occurance) D1 returns Jan-19-2007 However.....if you want the nth occurance of the day that is in the MONTH including the date in A1.... Then D1: =A1-DAY(A1)-WEEKDAY(A1-DAY(A1))+(7*(C1-1))+B1 Example: A1: Jan-08-2007 B1: 6 (meaning Friday) C1: 2 (find the 2nd occurance) D1 returns Jan-12-2007 Does that help? *********** Regards, Ron XL2002, WinXP "Deb" wrote: Hello Everyone, If I start with a specific date (01-08-07) how do I get to the second friday date of the month of (01-19-07) using Excel? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Friday in week number | New Users to Excel | |||
Find out first Friday every month | Excel Discussion (Misc queries) | |||
Find out first Friday every month | Excel Worksheet Functions | |||
Date Calculations | Excel Worksheet Functions | |||
Finding Friday | Excel Discussion (Misc queries) |