Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Warrior Pope
 
Posts: n/a
Default 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!

  #2   Report Post  
Jason Morin
 
Posts: n/a
Default

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!

.

  #3   Report Post  
Warrior Pope
 
Posts: n/a
Default

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!

.


  #4   Report Post  
Jason Morin
 
Posts: n/a
Default

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!

.


.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help: Can I change the date formula from mm/dd/yyyy to "mmm-yy" et wintersunshine Excel Discussion (Misc queries) 7 July 5th 05 09:44 PM
date format within a cell containing a formula Brushie76 Excel Discussion (Misc queries) 1 January 22nd 05 04:50 AM
What formula do I use to add to a date column in Excel? Donna at CMS Excel Discussion (Misc queries) 2 January 20th 05 12:59 PM
Formula with text and reference to a date cell [email protected] Excel Discussion (Misc queries) 1 January 11th 05 09:15 AM
Date Formula Robyn Bellanger Excel Discussion (Misc queries) 2 December 17th 04 08:25 PM


All times are GMT +1. The time now is 09:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"