Assigning a date that is before expiration date

[Visakha]

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

 

[Jacob Skaria]

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

 

[Visakha]

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]

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