You will need a definition of how the revenue is to be prorated, but let's
assume it's by the number of days in the contract. Therefore, let:

a1 = start date = date(6,1,6)
a2 = end date = date(6,2,5)
a3 = revenue = 1000
a4 = revenue /day = a3 / (a2-a1)
a5 = revenue in first month = a4 * (date(year(a1),month(a1)+1,0) - a1)
a6 = revenue in last month = a4 * day(a2)

It gets more complicated if the contract spans more than two months, but you can
extrapolate the above to accommodate.

--
Regards,
Fred


"Gazzr" wrote in message
...

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