Monthly dates on Reports

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
Thanks Don
This works better for me

Don Guillett said:
=J1+IF(WEEKDAY(J1+1)=7,3,1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Click to expand...
 
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
 
Back
Top