Thanks Ron,

I have tried this and tested this also against a date range of 21/5/09 -
29/08/09 and I get the answer of 30.

The missing part for me is where I can tell Excel which billing month I am
in, so if I am billing for August I am looking for the answer of 9 days but
this formula gives me 29 days. Any ideas?

Thanks


"Ron Rosenfeld" wrote:

On Mon, 29 Jun 2009 21:23:01 -0700, Mustang
wrote:

Hi there,

I am having problems finding a solution to a problem I have been given. I
have a workbook which on one sheet we log:

Job No Weekly $ Start Date End Date No of days

This information is filtered through to a monthly sheet where I filter by
job number. My problem is that I know the total number of days our equipment
has been hired for which is fine if they hire something for just one billing
month or part of that month BUT we run our invoicing from 21st month to 20th
of the next month.

So if we are charging for June this would be from 21/6/09 - 20/7/09. So if
the Start date is 21/6/09 but end date is 5/5/09 - how can I ask Excel to
work out just the number of days on hire for the June billing period?
Bearing in mind that the dates could also be say 21/6/09 - 29/6/09. I hope
this makes sense!

Any ideas on how I can ask Excel to work out based on the Month - how many
days a piece of equipment has been hired for?

Any ideas would be appreciated.


In general, you can use this formula:

=SUMPRODUCT(--(MONTH(ROW(INDIRECT(Start_Date&":"&End_Date)))=Mon thNumber))


So for June:

=SUMPRODUCT(--(MONTH(ROW(INDIRECT(Start_Date&":"&End_Date)))=6))



Note that for versions of Excel prior to 2007, you may run into the

"dreaded 4 Jun 2079" problem

after which this formula will no longer work :-))

--ron