Here's an interesting little problem for you. Attached is an example of what I'm trying to achieve.
The cells in purple at the top of the sheet contain the working hours of a member of staff (row 1 contains numerical values for each day, i.e. 2=Monday, 3=Tuesday in white etc). The cells in green on the left contain bank holidays for this year. The yellow cells contain dates that will be changed. In the blue cell I have a formula. The red cell is an example of the kind of answer I would require.
Essentially, what I'm doing is counting how many bank holidays fall between the dates entered in the yellow cells, and that figure is entered into the blue cell. In the example, the number of bank holidays between the 2 dates is 2. Changing the dates will adjust the answer.
What I would like is a formula that will take the two dates in yellow, scan the list of bank holidays, match the days on which they fall against the purple cells at the top, and add the hours worked on those days together.
So, in the example, the bank holidays fall on a monday and tuesday, therefore the answer (in the red cell) would be 11 as 5hrs are worked on Monday and 6hrs are worked on a tuesday.
I'm usually really good with formulas, but this one has me beaten.
Hope that makes some sense.
Bookmarks