Monthly dates on Reports

[Wanna Learn]

Hello I have a monthly report with worksheets for each working day of the
month, Monday thru Friday. I use the same report every month I just change
the month. the report tilte is the name as the day of the month e.g.
September 4 2007 the next sheet is September5 2007 etc.(I'm always one day
behind, on Sep 5 I will be doing report for Sep 4 ) On the first worksheet
of the month I type in the date. the second sheet of the month I entered the
formula =Sep407!J1+1 . The problem is that every monday I have to adjust the
formula, to compensate for Sat and Sun. Is there a formulat that if I enter
the date on the first worksheet it will automatically update the entire
workbook correctly. thanks

 

[Duke Carey]

If you make sure to have a list of holiday dates in the workbook, you can use

=workday("8/31/2007",1,range with holiday dates) to get the first working
day of each month (adjusting the date in quotes as appropriate)

Once you get the monthly starting date, each subsequent sheet can use this
same formula, but referencing the date on the earlier sheet.

 

[Wanna Learn]

Thanks Duke Carey
This works, but for my clarification, I still have to go in and adjust the
date in quotes in each worksheet. that is
=WORKDAY( "9/5/07",1)
=WORKDAY( "9/6/07",1)
=WORKDAY( "9/7/07",1)
thanks again

 

[Don Guillett]

=J1+IF(WEEKDAY(J1+1)=7,3,1)

 

[Wanna Learn]

Thanks Don
This works better for me

=J1+IF(WEEKDAY(J1+1)=7,3,1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Duke Carey]

No, just reference the formula on the previous worksheet, i.e., the one that
returns the date. So, if on the first worksheet you have this formula in A1

=workday("8/31/2007",1,range with holiday dates)

then on the next worksheet you would use

=workday(Sheet1!A1,1,range with holiday dates)

That way you only have to change ONE CELL each month

 

[Wanna Learn]

DuKe Carey
Yes! this definetly gives me the answer I need thanks