I have a spreadsheet that list when authorizations expire. Before the
expiration date, the authorization must be renewed in a meeting that occurs
every Wednesday. Thus far, the assigning of the renewal meetings has been
done manually. Is the a macro or lookup function that does this
automatically?

Example: if expiration is on 10/19/09, then assign to Wednesday date before
or on 10/19/09. Cell to show 10/21/09.

Thanks in advance!

[venkat1926]

suppose date is in A1

try this in B1

=IF(WEEKDAY(A1)<=4,A1+4-WEEKDAY(A1),A1+4-WEEKDAY(A1)+7)

you can copy B1 down

Hi Visakha

You example and description contradicts.. However both options are given
below. Test and feedback

> Example: if expiration is on 10/19/09, then assign to Wednesday date before
> or on 10/19/09. Cell to show 10/21/09.


Sub Macro2()
Dim myDate As Date
myDate = DateValue("10/14/2009")

MsgBox "Wednesday before or current: " & DateAdd("d", _
IIf(Weekday(myDate) < 4, -3, 4) - Weekday(myDate), myDate)

MsgBox "Wednesday after or current: " & DateAdd("d", _
IIf(Weekday(myDate) <= 4, 4, 11) - Weekday(myDate), myDate)

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Visakha" wrote:

> I have a spreadsheet that list when authorizations expire. Before the
> expiration date, the authorization must be renewed in a meeting that occurs
> every Wednesday. Thus far, the assigning of the renewal meetings has been
> done manually. Is the a macro or lookup function that does this
> automatically?
>
> Example: if expiration is on 10/19/09, then assign to Wednesday date before
> or on 10/19/09. Cell to show 10/21/09.
>
> Thanks in advance!
>
>

Hi Jacob,

Thanks for catching that--I meant before or on. And yes, this macro works
but I am wondering how to apply it a column of cells. Say the expiration
dates are listed in column b (reiterating to up to 1000). Is there a way to
show the assigned date in column C instead of a dialog box? I am fairly new
to macros but am enjoying them! Thanks so much!

"Jacob Skaria" wrote:

> Hi Visakha
>
> You example and description contradicts.. However both options are given
> below. Test and feedback
>
> > Example: if expiration is on 10/19/09, then assign to Wednesday date before
> > or on 10/19/09. Cell to show 10/21/09.
>
>
> Sub Macro2()
> Dim myDate As Date
> myDate = DateValue("10/14/2009")
>
> MsgBox "Wednesday before or current: " & DateAdd("d", _
> IIf(Weekday(myDate) < 4, -3, 4) - Weekday(myDate), myDate)
>
> MsgBox "Wednesday after or current: " & DateAdd("d", _
> IIf(Weekday(myDate) <= 4, 4, 11) - Weekday(myDate), myDate)
>
> End Sub
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Visakha" wrote:
>
> > I have a spreadsheet that list when authorizations expire. Before the
> > expiration date, the authorization must be renewed in a meeting that occurs
> > every Wednesday. Thus far, the assigning of the renewal meetings has been
> > done manually. Is the a macro or lookup function that does this
> > automatically?
> >
> > Example: if expiration is on 10/19/09, then assign to Wednesday date before
> > or on 10/19/09. Cell to show 10/21/09.
> >
> > Thanks in advance!
> >
> >

You dont need a VBA solution to do this. With dates in Column B starting from
cell B1; in C1 enter formula

=B1+IF(WEEKDAY(B1)<4,-3,4)-WEEKDAY(B1)
and copy down as required

If this post helps click Yes
---------------
Jacob Skaria


"Visakha" wrote:

> Hi Jacob,
>
> Thanks for catching that--I meant before or on. And yes, this macro works
> but I am wondering how to apply it a column of cells. Say the expiration
> dates are listed in column b (reiterating to up to 1000). Is there a way to
> show the assigned date in column C instead of a dialog box? I am fairly new
> to macros but am enjoying them! Thanks so much!
>
> "Jacob Skaria" wrote:
>
> > Hi Visakha
> >
> > You example and description contradicts.. However both options are given
> > below. Test and feedback
> >
> > > Example: if expiration is on 10/19/09, then assign to Wednesday date before
> > > or on 10/19/09. Cell to show 10/21/09.
> >
> >
> > Sub Macro2()
> > Dim myDate As Date
> > myDate = DateValue("10/14/2009")
> >
> > MsgBox "Wednesday before or current: " & DateAdd("d", _
> > IIf(Weekday(myDate) < 4, -3, 4) - Weekday(myDate), myDate)
> >
> > MsgBox "Wednesday after or current: " & DateAdd("d", _
> > IIf(Weekday(myDate) <= 4, 4, 11) - Weekday(myDate), myDate)
> >
> > End Sub
> >
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "Visakha" wrote:
> >
> > > I have a spreadsheet that list when authorizations expire. Before the
> > > expiration date, the authorization must be renewed in a meeting that occurs
> > > every Wednesday. Thus far, the assigning of the renewal meetings has been
> > > done manually. Is the a macro or lookup function that does this
> > > automatically?
> > >
> > > Example: if expiration is on 10/19/09, then assign to Wednesday date before
> > > or on 10/19/09. Cell to show 10/21/09.
> > >
> > > Thanks in advance!
> > >
> > >