Complex allocation of workdays within Gov. fiscal year
 

My problem comes in two parts:

1) I have a Networkdays formula that calculates the number of working days
for an employee
- A13 has the €œstart date€�
- A14 has the €œend date€�
- A15 has the Networkdays formula

I need a formula that will calculate the number of networkdays between the:
1) later of the €œstart date€� or April 1st, 2008 and the 2) earlier of the
€œend date€� and March 31, 2009.

In summary, with respect to the government fiscal year from April 1, 2008 to
March 31, 2009, I need to know how many working days an employee has worked
within a fiscal year considering that he might have started before or after
April 1st, 2008, and ended his assignment before or after March 31st, 2009. I
only want the number of days he worked within the fiscal year disregarding
days worked before or after this fiscal year. By the way, the fiscal year is
chosen from a drop-down list on the Summary sheet and this is given range
name of €œFiscal_Year€�. Can this solution be adapted to account for any fiscal
year that the user has chosen?

2) I have a list of the 12 months representing the fiscal year in the range
B19 to B30. In the adjacent range C19 to C30, I have the corresponding number
of workdays for each month (using the networkdays formula, which excludes
weekends).
e.g. April 22
May 22
. .
. .
March 22

I need to hide all the month(s) (by hiding the entire row) for which the
employee has not worked and show only the month(s) for which the employee has
worked. In addition, for the first month that the employee has worked, I need
the number of work days worked within this month (assuming that this date is
the later of April 1st, 2008). For the last month that the employee has
worked, I need the number of days worked (assuming that this date is earlier
than March 31st, 2009).

For example, lets assume that an employee started on May 15, 2008 and ended
on August 19, 2008. My list of months would look like this:
May 12 €˜because the employee only worked 12 days in May since he
started on May 15th)
Jun 21
Jul 23
Aug 9 €˜the employee only worked 9 days in August
All other months (and the rows in which they are contained) would be hidden.

What combination of formulas and Visual Basic would solve this problem?

I REALLY would appreciate any help given!! If I could buy you a beer in a
gesture of gratitude, I would.... Two beers?

Kent Lysell
IBM Financial Systems Consultant
Ottawa, Ontario
C: 613.262-6917