# (IF(AND)) contingent upon date

1. ## (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.

2. ## 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?

3. ## 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. ## Re: (IF(AND)) contingent upon date

Does this do it for you?

``Please Login or Register  to view this content.``

5. ## Re: (IF(AND)) contingent upon date

Originally Posted by katja328
=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. ## Re: (IF(AND)) contingent upon date

This one definitely worked.

Now on to trying to understand it.

Thanks!!!!!

Originally Posted by NBVC
Does this do it for you?

``Please Login or Register  to view this content.``

7. ## 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. ## 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. ## Re: (IF(AND)) contingent upon date

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

Thanks!

10. ## 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. ## Re: (IF(AND)) contingent upon date

Works! Thanks!!!

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

#### 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