Thank you Max,

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

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.

Thanks




"Max" wrote:

Think you could try using DATE

Assuming startdates in C2 down, enddates in D2 down
(where the startdates are always the 21st of the month?)

Then in E2, E2 formatted as general/number, then copied down:
=IF(D2DATE(YEAR(C2),MONTH(C2)+1,DAY(C2)-1),DATE(YEAR(C2),MONTH(C2)+1,DAY(C2)-1)-C2+1,D2-C2+1)

Test it out and satisfy yourself that it's returning correctly
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Mustang" wrote:
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.