Thank you for your quick response,

I think that there is a misunderstanding here. The Hire Payment is paid in
advance therefore the calculations fall as follows:

15-Jan-07 to 29-Jan-07 15 days 150,000
29-Jan-07 to 12-Feb-07 15 days 150,000 300,000 January
12-Feb-07 to 26-Feb-07 15 days 150,000
26-Feb-07 to 12-Mar-07 15 days 150,000 300,000 February

Payments or receipts for January are made on the 15 Jan and 29 Jan so the
receipts for that month are USD 300k. The formula you suggested works the
total allocation as 17 days for January which gives you a total cash
collection of USD 170k only.

Collections will be made from a number of clients with varying dates. Some
collections are paid in advance others are paid in arrears so each entry must
be tested as to the payments date. Finally when all entries are collected
into a table a SUMIF possibly an Array formula will be needed to collect all
of the information for the specific month/period.

My apologies if I did not explain the problem clearly from the start.
Many thanks indeed/sgl

"Bob Phillips" wrote:

Assuming that data is in A1:A4, try this

G1:
=IF(DATE(YEAR($A$1),MONTH($A$1)+COLUMN(A$1)-1,1)$A$2,"",DATE(YEAR($A$1),MONTH($A$1)+COLUMN(A$ 1)-1,1))

copy G1 across to R1

G2: =IF(G1="","",DATE(YEAR($A$1),MONTH($A$1)+COLUMN(A$ 1),1)-$A$1)
H2:
=IF(H1="","",MIN((DATE(YEAR($A$1),MONTH($A$1)+COLU MN(B$1),1)-$A$1-SUM($G$2:G2)),$A$3-SUM($G$2:G2)))

copy H2 acroos to R2

G3: =IF(G2="","",G2*$A$4)

copy G3 across to R3

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"sgl" wrote in message
...
Hi All,

I am developinga spreadsheet where the user will enter the following data
for each entry which will accumulate in a table.

Start Date 15-Jan-07
End Date 22-Oct-07
Total days 281 (calculation)
Hire Payment per day 10,000
Total Hire Received 2,810,000 (calculation)
Payable every (Days) 15
Hire Paid in Advance (this could also be in
arrears)

How can I develop a formula that will automatically calculate, in a
tabulated format going across in months, what is payable and what should
be
allocated to each specific month.

Thank you and a Happy, Peaceful and Productive New Year to all of you/sgl