On Wed, 1 Jul 2009 16:43:01 -0700, Mustang
wrote:

Hi Ron,

On my first sheet (Quoted Job No Master) the dates are laid out as:

H I
1
2 Start Date End Date
3 21/05/09 29/08/09

On the monthly job sheet (there is one sheet that is re-used each month) to
try and work out the number of days on hire for the billing month I have
tried your formula and for August it looks like:

=SUMPRODUCT(--(MONTH(ROW(INDIRECT('Quoted Job No Master'!H3&":"&'Quoted Job
No Master'!I3)))=8))

Hopefully you will be able to let me know what I have done incorrectly.

Thanks so much for all your help.

OK, you are not doing anything wrong. It was ME who did not read your initial
specifications closely enough. In particular, I missed the part about your
billing month starting on the 21st day of the month and ends on the 20th day of
the succeeding month.

Let me suggest this formula:

=SUMPRODUCT((ROW(INDIRECT(StartDate&":"&EndDate)) =DATE(YEAR(K3),MONTH(K3),21))*
(ROW(INDIRECT(StartDate&":"&EndDate))<=DATE(YEAR(K 3),MONTH(K3)+1,20)))

In this instance, K3 has some date (it can be any date) in the month of
interest. You could format that cell to show just the month name, or month and
year.

And by the way, this formula DOES give a result of 9 for the month of August,
which I now realize covers 21-Aug through 29-Aug inclusive.



--ron