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" <(E-Mail Removed)> wrote in message
news:FFF4CB14-9549-4B50-A6F4-(E-Mail Removed)...
> 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.
Similar Threads
