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

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


"Wanna Learn" wrote in message
...
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