Hi

I hope someone can help. Per the attached screen shot I am trying to implement date specific formulas in columns W through AK.

Column O is the total funding available. A 20% retention (Column R) and 5% audit check amount (Column Q) are withheld from this total until the end of the contract period leaving column P as the amount to work with.

The monthly amount (Column U) is calculated by dividing Column P by Column T.


The first payment is dependant on whether column V matches the cells W1 through AK1 and is 2 months up front. The following months will be the monthly amount in column U.
When the sum of these payments matches the amount of Column P then the Retention amount is released. Lastly the audit amount it released. The sum of all these amounts should total the amount in Column O.

The formulas I have tried to use are as follows:

W2 (This is an easy calculation and works fine.)
=IF(DATE(YEAR($V2),MONTH($V2),DAY(0))=DATE(YEAR(W$ 1),MONTH(W$1),DAY(0)),$U2*2,"")

X2
=IF(DATE(YEAR($V2),MONTH($V2),DAY(0))=DATE(YEAR(x$ 1),MONTH(x$1),DAY(0)),U2*2,IF(SUM($W2)=$P2,$R2,IF( SUM($W2)=SUM($R2+$P2),$Q2,IF(SUM($W2)=$O2,"",$U2)) ))

Y2
=IF(DATE(YEAR($V2),MONTH($V2),DAY(0))=DATE(YEAR(y$ 1),MONTH(y$1),DAY(0)),U2*2,IF(SUM($W2:$Y2)=$P2,$R2 ,IF(SUM($W2:$Y2)=SUM($R2+$P2),$Q2,IF(SUM($W2:$y2)= $O2,"",$U2))))

etc.

(Note: The attachment is how the spreadsheet should look but I have been unable to match this.)

Thanks in advance

Attached Images

Capture.jpg (25.1 KB, 22 views)