+ Reply to Thread
Results 1 to 2 of 2

Create a formula with date constraints

  1. #1
    Registered User
    Join Date
    01-28-2005
    Posts
    1

    Create a 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. #2
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    Let's assume your sheet is set up like this:

    A2=Invoice receipt date
    B2=First date of Blackout period
    C2=Last date of Blackout period
    E2 will be the Pay by date, so in E2 place this formula:

    =IF(A2+14>=B2,IF(A2+14<=C2,B2-3,A2+14),A2+14)

    Therefore, if adding 14 days to your receipt date falls within your blackout dates (inclusive), you'll subtract 3 from the first blackout date. Otherwise, you simply add 14 to your receipt date for pay on date.

    HTH
    Bruce
    The older I get, the better I used to be.
    USA

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1