Excel formula with date constraints

[Guest]

Could use some help with the following:

I created a spreadsheet to track payments made to contractors. In this
spreadsheet I have a simple formula that tells when the payment has to be
made based on the date it was rec'd from the contractor and adding on the
payment terms.

Now what I have to do is add in some date constraints. Our paying office
only processes payments between certain windows during the month. Lets say
that for this month the payment window closes Jan 27th to Feb 2nd. What I
need to do is if after someone enters the date the payment is rec'd and the
14 days is added for the payment terms, should the date the payment is made
end up falling within the payment black-out period (Jan 27th to Feb 2nd), I
then need excel to adjust the must be paid by date to 3 days prior to the
black-out period.

Hope this makes sense....I've tried a whole bunch of different things and
have looked online at several sites with no luck. Would SERIOUSLY appreciate
it if someone can help me!

Thanks!

 

[Jason Morin]

Try:

=IF(AND(A1+14>=$E$1,A1+14<=$F$1),$E$1-3,A1+14)

where E1 is the start of the blackout period, F1 is the
end, and A1 is the date received.

The only thing to note is that a receive date of 1/11/04
and 1/12/04 generates a payment date of 1/25/04 and
1/26/04, both less than 3 days of the start of the
blackout.

HTH
Jason
Atlanta, GA

 

[Guest]

This is the message I get:

cannot calculate formula. Cell references in the formula refer to the
formulas results, creating a circular reference.

If possible, can you give me an email address, I can send the spreadsheet to
you and you can take a look at it because maybe I am not exactly explaining
everything correctly???

 

[Jason Morin]

Go ahead and send it. Replace OPPOSITEOFCOLD with you
know what.

Jaosn