Second Friday

D

Deb

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
 
V

vezerid

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
 
B

Bernard Liengme

Try this
=DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(A1),5,4,3,2,1,0,6)+7
best wishes
 
R

Ron Coderre

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
 
R

Ron Coderre

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
 
Top