+ Reply to Thread
Results 1 to 11 of 11

(IF(AND)) contingent upon date

  1. #1
    Registered User
    Join Date
    04-06-2009
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    34

    (IF(AND)) contingent upon date

    =IF(AND(F7>DATE(Year(F3),Month(F3),Day(F3)),(F7<Date(Year(F3),12,31)),-H12,0))

    F3=Start Date - currently 6/1/09
    F7=End Date
    H12 = deposit amount 3000

    Trying to get to the following:
    If my End Date (F7) is between my start date (F3) and the 31st of December of the start date's year (Date(Year(F3),12,31) then put negative deposit in cell (-H12) otherwise put 0

    the following year would the formula should be between the next year 1/1/10 (Date(Year(F3)+1,1,1) and 12/31/2010 (Date(Year(F3)+1,12,31) then return -H12 otherwise put 0

    Thanks.
    Last edited by katja328; 04-09-2009 at 01:47 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: (IF(AND)) contingent upon date

    Not sure what the question is... won't F7 have next year's date in, next year?

    Do you have =Today() in F7?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    04-06-2009
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: (IF(AND)) contingent upon date

    Sorry for the confusion

    I am collecting a security deposit (H12)

    My lease start date F3

    My lease end date F7

    I have cash flow numbers for

    2009 2010 2011

    if my lease end date F7 is 3/31/2011 then I will have to return the security deposit in year 2011 so the formula in 2011 should read

    If End date F7 is between the 1/1/11 and 12/31/11 then return H12 in year 2011

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: (IF(AND)) contingent upon date

    Does this do it for you?

    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: (IF(AND)) contingent upon date

    Quote Originally Posted by katja328 View Post
    =IF(AND(F7>DATE(Year(F3),Month(F3),Day(F3)),(F7<Date(Year(F3),12,31)),-H12,0))

    F3=Start Date - currently 6/1/09
    F7=End Date
    H12 = deposit amount 3000

    Trying to get to the following:
    If my End Date (F7) is between my start date (F3) and the 31st of December of the start date's year (Date(Year(F3),12,31) then put negative deposit in cell (-H12) otherwise put 0

    the following year would the formula should be between the next year 1/1/10 (Date(Year(F3)+1,1,1) and 12/31/2010 (Date(Year(F3)+1,12,31) then return -H12 otherwise put 0

    Thanks.
    Looks like you have the paranthesis in the wrong place. There should be another ")" before the ",-H12" and only 1 afer the ",0"

    And To make it easier to read why don't you do this:

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-06-2009
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: (IF(AND)) contingent upon date

    This one definitely worked.

    Now on to trying to understand it.

    Thanks!!!!!

    Quote Originally Posted by NBVC View Post
    Does this do it for you?

    Please Login or Register  to view this content.

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: (IF(AND)) contingent upon date

    Basically it's your formula with some built-in IF() statements to determine the month and day parts of the DATE() functions...it decides if you should use today's date or the first of next year based on the year of the date in F7.

  8. #8
    Registered User
    Join Date
    04-06-2009
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: (IF(AND)) contingent upon date

    I spoke too soon. It works great until I hit a year after the lease expiration date.

    I will have different leases, going from 24 months to 80 months.

    This lease is 36 months and the formula below is right in spitting out negative H12 in the correct year

    =IF(AND($F$7>DATE(YEAR($F$3),IF(YEAR($F$7)>YEAR(TODAY()),1,MONTH($F$3)),IF(YEAR($F$7)>YEAR(TODAY()),1,DAY($F$3))),($F$7<DATE(YEAR($F$3)+3,12,31))),-$H$12,0)

    however, the following year

    =IF(AND($F$7>DATE(YEAR($F$3),IF(YEAR($F$7)>YEAR(TODAY()),1,MONTH($F$3)),IF(YEAR($F$7)>YEAR(TODAY()),1,DAY($F$3))),($F$7<DATE(YEAR($F$3)+4,12,31))),-$H$12,0)

    It spits it out too. Even though there is no lease ongoing.

    I am trying to upload my file, but it's 2MB and I need to compress it first

  9. #9
    Registered User
    Join Date
    04-06-2009
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: (IF(AND)) contingent upon date

    with attachment. Hopefully you'll see where my problem lies.

    Thanks!
    Last edited by katja328; 04-09-2009 at 01:48 PM.

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: (IF(AND)) contingent upon date

    Not sure I am understanding fully but maybe this in C39 copied across to H39?

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    04-06-2009
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: (IF(AND)) contingent upon date

    Works! Thanks!!!

+ 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