Working Days in a Named Month

[Andy Roberts]

I have a spreadsheet with 12 tabs (1 for each month of our financial year).
I have a cell in each sheet which populates itself with the name of the
sheet, so the first sheet is called August 2012 and I use the following
formula in a cell to have that cell display "August 2012" (or whatever the
sheet name is)

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

This works fine. I now want to move things on a stage and have another cell
calculate the number of working days based on the value taken from the sheet
name, so August 2012 would show 22 (there is a bank holiday in August plus 4
weekends = 9 days. Therefore 31-9=22).

Is this possible with a formula as on the next sheet I want the cell to
return the answer of 20 as September 2012 only has 20 working days.

Regards

Andy Roberts
Win XP Pro
Office 2010

 

[Ron Rosenfeld]

I have a spreadsheet with 12 tabs (1 for each month of our financial year).
I have a cell in each sheet which populates itself with the name of the
sheet, so the first sheet is called August 2012 and I use the following
formula in a cell to have that cell display "August 2012" (or whatever the
sheet name is)

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

This works fine. I now want to move things on a stage and have another cell
calculate the number of working days based on the value taken from the sheet
name, so August 2012 would show 22 (there is a bank holiday in August plus 4
weekends = 9 days. Therefore 31-9=22).

Is this possible with a formula as on the next sheet I want the cell to
return the answer of 20 as September 2012 only has 20 working days.

Regards

Andy Roberts
Win XP Pro
Office 2010

=NETWORKDAYS(TRIM(RIGHT(SUBSTITUTE(CELL("filename"),"]",REPT(" ",99)),99)),
EOMONTH(TRIM(RIGHT(SUBSTITUTE(CELL("filename"),"]",REPT(" ",99)),99)),0),Holidays)

Holidays is a range where all of your holidays are listed. It can be on a separate worksheet (or even in a separate workbook, but that might not be convenient).