+ Reply to Thread
Results 1 to 2 of 2

multiple condition if formula that will take into consideration month only in a date

  1. #1
    Registered User
    Join Date
    02-27-2013
    Location
    West Bountiful, Utah
    MS-Off Ver
    Excel 2007
    Posts
    10

    multiple condition if formula that will take into consideration month only in a date

    Hi, I need help with a formula that will compute contract amounts that are paying out for a given month, depending on what is listed as the contract renew date and how often the contract renews.

    Assume the contract date is in C1, contract renew date is in C2, Contract term is a drop down menu in C3 that has two options of text, “6 Months” or “12 Months”. C4 gives the contract amount. There are then dollar amounts in C5 and C6 that are only taken into consideration if they are $0.00. In B1 will be the run date or the date I want to pull my info for.

    I basically need a formula in C7 that will return the contract amount from C4 taking into account these conditions:
    If there is a dollar amount in either C5 or C6, return “$0.00” in C7. But if C5 and C6 are both zero, than:
    If C3 = “12 Months” and B1 is in the same month as C2 (I don’t want it to take into account the year of day of the date), than return the amount listed in C4 in cell C7. Or if C3 = “6 Months”, and B1 is in the same month as C2 or if B1 is 6 months ahead of the month listed in C2 (again not taking into account the day or year), than return the amount in C4 in cell C7.
    So basically if the contract amount in C4 was $100, and B1 3/1/2014 and the contract renew date is C2 is 9/15/2013, I still want $100 to appear in C7, even though there is only technically 5 and a half months in between the dates of 9/15/2013 and 3/1/2014. I want it to look at these dates and add six months to the month of September and then look at cell B1 and as long as that date is a date in March, return the contract amount in cell C7. I also want $100 to appear in C7 if the date in B1 was a September date. In the situation where C3 is set to 12 months, if C2 is a September date than $100 will only appear in C7 if B1 is also a September date.

    And one last thing, I'm going to somewhat withdraw my request that the year shouldn't be taken into account. This is only partially true. It just occurred to me that the $100 should only appear provided the date in B1 is in the same year or in a subsequent year AFTER the year of the contract renew date in C2. So for instance if, C2 is 9/15/2013 and I put in 03/01/2013 in B1, it should NOT calculate the $100 in C7 since the contract technically would not have renewed in March of '13 as it didn't exist until Sept of '13.

    Any help would be greatly appreciated! I'm been beating my head against a wall for a couple weeks now and I'm ready to throw in the towel.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: multiple condition if formula that will take into consideration month only in a date

    Hi,

    I think you'd better upload the workbook so that we can see your request in context.
    You must also manually add the results you expect to see for your various permutations and explain with a text note referring to the cells used in your calculation how you arrived at the result.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to take multiple cells into consideration while calculating sum Or addition.
    By warriorpoet7176 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-30-2014, 02:15 AM
  2. [SOLVED] Sum after consideration of date ranges???
    By mandiedwards in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-16-2013, 12:14 PM
  3. Replies: 5
    Last Post: 10-04-2012, 07:06 AM
  4. % to Volume Formula taking into consideration days worked
    By kwilliams5675 in forum Excel General
    Replies: 7
    Last Post: 04-20-2010, 03:26 PM
  5. VBA Coding: Date Consideration
    By dzuy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-30-2006, 06:10 AM

Tags for this Thread

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