Weekday function Not right

[Wanna Learn]

Hello I have a workbook and one worksheet for each weekday in a month .
Using the example for May 2008. On Cell J1 I have the date 5/1/08. On the
second sheet on J1 I have the following formula
='05-01-08'!J1+IF(WEEKDAY('05-01-08'!J1+1)=7,2,1) and it gives me 5/2/08 .
sheet 3 J1='05-01-08'!J1+IF(WEEKDAY('05-01-08'!J1+1)=7,2,2) gives me 5/3/05 .
sheet 4 ='05-01-08'!J1+IF(WEEKDAY('05-01-08'!J1+1)=7,2,4) and it gives me May
5, 2008.
What I've been doing is manually changing the last number of the formula
to give me the day I want. What is the correct formula so that I do not
have to manually change each formula in every sheet (every month) thanks in
advance

 

[Bernie Deitrick]

Use a macro:

Sub ChangeDates()
Dim i As Integer
Dim myDate As Date
myDate = DateSerial(Year(Date), Month(Date) + IIf(MsgBox("For this Month?", vbYesNo) = vbYes, 0, 1),
1)
If Weekday(myDate, vbSaturday) <= vbMonday Then myDate = myDate + 3 - Weekday(myDate, vbSaturday)
For i = 1 To Worksheets.Count
Worksheets(i).Range("J1").Value = myDate
myDate = myDate + IIf(Weekday(myDate + 1, vbSunday) = vbSaturday, 3, 1)
Next i
End Sub

Answer Yes if you want the dates for the current month, or No if you want the dates for next month.

HTH,
Bernie
MS Excel MVP