Try this example:

In A1:C5 enter this table:
Period______MthBeg_______MthEnd
200512_____01-Dec-05____31-Dec-05
200601_____01-Jan-06_____27-Jan-06
200602_____28-Jan-06_____24-Feb-06
200603_____25-Feb-06_____24-Mar-06

E1: StartDate
E2: 01-Feb-06

F1: EndDate
F2: 25-Feb-06

G1: Days
G2: =+F2-E2+1

H1: Fee
H2: 1,000

I1: 200601
J1: 200602
K1: 200603

I2:
=MAX(MIN(VLOOKUP(I$1,$A$2:$E$5,3,1),$F2)-MAX(VLOOKUP(I$1,$A$2:$E$5,2,1),$E2)+1,0)*$H2/$G2

Copy I2 across through K2

These are the returned values
I1: 0
J1: 960
K1: 40
Totalling: 1,000

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Gazzr" wrote:


Hi,

Hope you can help. We have sales reps that sell online advertising
space that can start and finish when the customer wants. EG booking
from 06/01/06 to 05/02/06 for a total of $1,000.

The accounts department needs to show the revenue in their accounting
month. In this case Jan month is from 01/01/06 to 27/01/06, Feb month
is 28/01/06 to 24/02/06.

So in this case some of the $1000 would be Jan revenue and some Feb
revenue.

What sort of formula could I use to show what accounting month the
revenue should appear in based on the start and finish date of the
advertising?

i.e The sales rep can enter the contract amount and the start and
finish date of the contract and the formula will work out what
accounting month the revenue should appear in and show this in the
appropriate month columns.

Regards
Garry


--
Gazzr
------------------------------------------------------------------------
Gazzr's Profile: http://www.excelforum.com/member.php...o&userid=31075
View this thread: http://www.excelforum.com/showthread...hreadid=507492