Hi James

I set up the end dates of your tariff periods and the period number in
cells A2:B9 and named this Periods.
Your full range of dates with start and end dates for each period, I
copied to cells D2:E9 having adjusted the value in cell D3 to be
14/04/06 not 13/04/06
In C2 I entered the following formula
=E2-D2+1 and copied down through C3:C9
I then named range B2:E9 as Tariffs

I used F2 to Enter Start Date and G2 to enter End Date
In F1 I entered the formula
=VLOOKUP(F2,Periods,2)+1 and copied across to G1

In Cell H2 I entered the following formula

=IF(AND($F1<B2,$G1B2),VLOOKUP(B2,tariff,2,0),
IF($F1=B2,VLOOKUP($F1,tariff,2,0)-($F2-VLOOKUP($F1,tariff,3,0)),
IF($G1=B2,VLOOKUP($G1,tariff,2,0)-(VLOOKUP($G1,tariff,4,0)-$G2)+1,0)))

and copied down through cells H3:H9

Then inputting start and end dates to F2 and G2 gives the number of days
falling into each tariff period.


--
Regards

Roger Govier


"jamesjohn"
wrote in message
...

Roger,

I haven't made myself clear, because you have sent me the same formula
as for finding how many days in each month.

The table in my post is the start and end dates of the tarifs.

You will see that some tarifs only last a couple of weeks and are in
the same month and other tarifs take in dates from 3 different months.

I need to calculate how many days of each month are in each different
tarif.

Hope this makes sense !


--
jamesjohn
------------------------------------------------------------------------
jamesjohn's Profile:
http://www.excelforum.com/member.php...fo&userid=9692
View this thread:
http://www.excelforum.com/showthread...hreadid=545300