Excel formula with date constraints

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!

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

-----Original Message-----
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!

.

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" wrote:

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

-----Original Message-----
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!

.

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

Jaosn

-----Original Message-----
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" wrote:

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

-----Original Message-----
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!

.


.