I want these following dates
Friday, March 19, 2010
Friday, June 18, 2010
Friday, September 17, 2010
Friday, December 17, 2010
Friday, March 18, 2011
Friday, June 17, 2011
Friday, September 16, 2011
Friday, December 16, 2011
Friday, March 16, 2012
Friday, June 15, 2012
Friday, September 21, 2012
Friday, December 21, 2012
Friday, March 15, 2013
Friday, June 21, 2013
to be converted to next mondays like as given
below.
Monday, March 15, 2010
Monday, June 14, 2010
Monday, September 13, 2010
Monday, December 13, 2010
Monday, March 14, 2011
Monday, June 13, 2011
Monday, September 19, 2011
Monday, December 19, 2011
Monday, March 19, 2012
Monday, June 18, 2012
Monday, September 17, 2012
Monday, December 17, 2012
Monday, March 18, 2013
Monday, June 17, 2013
by changing the following VBA code which generates
the Fridays.

Function vbaThirdFriday(ByVal aDate As Date) As Date
On Error GoTo ErrorLabel
Dim Roll As Date, roll2 As Double
Roll = DateSerial(Year(aDate), Month(aDate), 15)
Debug.Print Roll
vbaThirdFriday = Roll + (13 - Weekday(Roll)) Mod 7
roll2 = (13 - Weekday(Roll)) Mod 7
Debug.Print vbaThirdFriday, roll2
Exit Function
ErrorLabel:
MsgBox "error in vbaThirdFriday( " + Str(aDate) + " )"
vbaThirdFriday = aDate
End Function
Thank YOU all Expert Sirs. Please help.

Hi,

How about a more generic function. This will return the Nth instance of any
weekday given a date.

Call with
=NthWkday(2,1,A1)

where
2 is the instance you want
1 is the day of the week in the range of 1 to 7 and 1=Sunday
A1 contains a valid date

Note I haven't trapped for invalid dates such asking for the 5 instance of a
date in a month if there aren't 5 of those in a month. The code could easily
be modified to do this.

Function NthWkDay(Instance, WkDay As Long, Dt As Date)
Dim FirstOfMonth As Date
FirstOfMonth = DateSerial(Year(Dt), Month(Dt), 1)
If Weekday(FirstOfMonth, vbSunday) < WkDay Then
FirstOfMonth = FirstOfMonth + (WkDay - Weekday(FirstOfMonth, vbSunday))
ElseIf Weekday(FirstOfMonth, vbSunday) WkDay Then
FirstOfMonth = FirstOfMonth + (WkDay + 7 - Weekday(FirstOfMonth,
vbSunday))
End If
NthWkDay = FirstOfMonth + (Instance - 1) * 7
End Function

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Beetal" wrote:

I want these following dates
Friday, March 19, 2010
Friday, June 18, 2010
Friday, September 17, 2010
Friday, December 17, 2010
Friday, March 18, 2011
Friday, June 17, 2011
Friday, September 16, 2011
Friday, December 16, 2011
Friday, March 16, 2012
Friday, June 15, 2012
Friday, September 21, 2012
Friday, December 21, 2012
Friday, March 15, 2013
Friday, June 21, 2013
to be converted to next mondays like as given
below.
Monday, March 15, 2010
Monday, June 14, 2010
Monday, September 13, 2010
Monday, December 13, 2010
Monday, March 14, 2011
Monday, June 13, 2011
Monday, September 19, 2011
Monday, December 19, 2011
Monday, March 19, 2012
Monday, June 18, 2012
Monday, September 17, 2012
Monday, December 17, 2012
Monday, March 18, 2013
Monday, June 17, 2013
by changing the following VBA code which generates
the Fridays.

Function vbaThirdFriday(ByVal aDate As Date) As Date
On Error GoTo ErrorLabel
Dim Roll As Date, roll2 As Double
Roll = DateSerial(Year(aDate), Month(aDate), 15)
Debug.Print Roll
vbaThirdFriday = Roll + (13 - Weekday(Roll)) Mod 7
roll2 = (13 - Weekday(Roll)) Mod 7
Debug.Print vbaThirdFriday, roll2
Exit Function
ErrorLabel:
MsgBox "error in vbaThirdFriday( " + Str(aDate) + " )"
vbaThirdFriday = aDate
End Function
Thank YOU all Expert Sirs. Please help.

I believe this shorter one-liner function will do what your posted function
does...

Function NthWkDay(Instance, WkDay As Long, Dt As Date)
NthWkDay = DateSerial(Year(Dt), Month(Dt), 1 + 7 * Instance) - _
Weekday(DateSerial(Year(Dt), Month(Dt), 8 - WkDay))
End Function

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
Hi,

How about a more generic function. This will return the Nth instance of
any
weekday given a date.

Call with
=NthWkday(2,1,A1)

where
2 is the instance you want
1 is the day of the week in the range of 1 to 7 and 1=Sunday
A1 contains a valid date

Note I haven't trapped for invalid dates such asking for the 5 instance of
a
date in a month if there aren't 5 of those in a month. The code could
easily
be modified to do this.

Function NthWkDay(Instance, WkDay As Long, Dt As Date)
Dim FirstOfMonth As Date
FirstOfMonth = DateSerial(Year(Dt), Month(Dt), 1)
If Weekday(FirstOfMonth, vbSunday) < WkDay Then
FirstOfMonth = FirstOfMonth + (WkDay - Weekday(FirstOfMonth, vbSunday))
ElseIf Weekday(FirstOfMonth, vbSunday) WkDay Then
FirstOfMonth = FirstOfMonth + (WkDay + 7 - Weekday(FirstOfMonth,
vbSunday))
End If
NthWkDay = FirstOfMonth + (Instance - 1) * 7
End Function

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Beetal" wrote:

I want these following dates
Friday, March 19, 2010
Friday, June 18, 2010
Friday, September 17, 2010
Friday, December 17, 2010
Friday, March 18, 2011
Friday, June 17, 2011
Friday, September 16, 2011
Friday, December 16, 2011
Friday, March 16, 2012
Friday, June 15, 2012
Friday, September 21, 2012
Friday, December 21, 2012
Friday, March 15, 2013
Friday, June 21, 2013
to be converted to next mondays like as
given
below.
Monday, March 15, 2010
Monday, June 14, 2010
Monday, September 13, 2010
Monday, December 13, 2010
Monday, March 14, 2011
Monday, June 13, 2011
Monday, September 19, 2011
Monday, December 19, 2011
Monday, March 19, 2012
Monday, June 18, 2012
Monday, September 17, 2012
Monday, December 17, 2012
Monday, March 18, 2013
Monday, June 17, 2013
by changing the following VBA code which
generates
the Fridays.

Function vbaThirdFriday(ByVal aDate As Date) As Date
On Error GoTo ErrorLabel
Dim Roll As Date, roll2 As Double
Roll = DateSerial(Year(aDate), Month(aDate), 15)
Debug.Print Roll
vbaThirdFriday = Roll + (13 - Weekday(Roll)) Mod 7
roll2 = (13 - Weekday(Roll)) Mod 7
Debug.Print vbaThirdFriday, roll2
Exit Function
ErrorLabel:
MsgBox "error in vbaThirdFriday( " + Str(aDate) + " )"
vbaThirdFriday = aDate
End Function
Thank YOU all Expert Sirs. Please help.