Date Range If Statement with 3 Possible Outcomes - PLEASE HELP!

[ironman arms]

I am trying to write a formula that will test if a re-fi date is in an annual date range (eg. 01/01/xx-12/31/xx). I need it to return the remaining balance at different dates or "0" depending on the outcomes.

IF Re-Fi Date is > Annual period = Balance at the end of that year
IF Re-Fi Date is =>WITHIN<= Annual period = Balance AT Re-Fi date itself
IF Re-Fi Date is < 12/31 of Annual period = Return "0" balance. (Meaning it was paid off)

My look-ups work fine, but having trouble getting the date range tests to work... I've replaced cells with the actual dates below. Any help would be greatly appreciated!!


=IF(04/01/2025>=01/01/2024,(-LOOKUP(12/31/2024,'Loan A - Amo'!$E$15:$E$314,'Loan A - Amo'!$S$15:$S$314))), IF('04/01/2025>=01/01/2024, AND(04/01/2025<=12/31/2024,(-LOOKUP(04/01/2025,'Loan A - Amo'!$E$15:$E$314,'Loan A - Amo'!$S$15:$S$314)))), IF(04/01/2025>12/31/2024,"-")

 

[etaf]

those formulas and brackets look worng

=IF(04/01/2025>=01/01/2024,(-LOOKUP(12/31/2024,'Loan A - Amo'!$E$15:$E$314,'Loan A - Amo'!$S$15:$S$314))), IF('04/01/2025>=01/01/2024, AND(04/01/2025<=12/31/2024,(-LOOKUP(04/01/2025,'Loan A - Amo'!$E$15:$E$314,'Loan A - Amo'!$S$15:$S$314)))), IF(04/01/2025>12/31/2024,"-")
the brackets highlighted red - closed the IF
and a ' before the date, the lst IF has no false condition

is that just an error typing here ?

=IF(04/01/2025>=01/01/2024,(-LOOKUP(12/31/2024,'Loan A - Amo'!$E$15:$E$314,'Loan A - Amo'!$S$15:$S$314)), IF('04/01/2025>=01/01/2024, AND(04/01/2025<=12/31/2024,(-LOOKUP(04/01/2025,'Loan A - Amo'!$E$15:$E$314,'Loan A - Amo'!$S$15:$S$314)))), IF(04/01/2025>12/31/2024,"-")

IF all the lookups are OK check the brackets around the IFs are all set out correctly
I usually use note pad and split it all out -

 

[ironman arms]

Yes, sorry typing errors. My main issue is correctly stringing the inequality IF statements for the date ranges... I don't think I am putting them together right.

 

[etaf]

04/01/2025>=01/01/2024

so that should refer to the Re-Fi Date cell
so
Re-Fi Date cell >= Annual period = if true then do the lookup

then the range
IF Re-Fi Date is =>WITHIN<= Annual period

AND( re-fi date cell >= DATE1 , re-fi date cell <= DATE2 ) so date 1 and 2 are your lower and upper limits

and next no need to test its 0
or you could test
Re-Fi Date cell is < 12/31 of Annual period


for the first lookup
04/01/2025>=01/01/2024
then thats true and so should run
this lookup
(-LOOKUP(12/31/2024,'Loan A - Amo'!$E$15:$E$314,'Loan A - Amo'!$S$15:$S$314))

your next test
IF('04/01/2025>=01/01/2024, AND(04/01/2025<=12/31/2024,

you are testing JUST 04/01/2025>=01/01/2024
and then if thats true - which it is
doing an AND
AND(04/01/2025<=12/31/2024
so would return - FALSE

AND would never goto the FALSE section to run the lookup

you next IF
IF(04/01/2025>12/31/2024,"-")
is also true
so returns a -
and no false to run
so returns FALSE

you need to break the IFs down into there
(TEST, TRUE , FALSE) sections


=IF(04/01/2025>=01/01/2024,(-LOOKUP(12/31/2024,'Loan A - Amo'!$E$15:$E$314,'Loan A - Amo'!$S$15:$S$314))), IF('04/01/2025>=01/01/2024, AND(04/01/2025<=12/31/2024,(-LOOKUP(04/01/2025,'Loan A - Amo'!$E$15:$E$314,'Loan A - Amo'!$S$15:$S$314)))), IF(04/01/2025>12/31/2024,"-")

=
IF(
Test
04/01/2025>=01/01/2024,
True
(-LOOKUP(12/31/2024,'Loan A - Amo'!$E$15:$E$314,'Loan A - Amo'!$S$15:$S$314))), note the -
FALSE
IF('04/01/2025>=01/01/2024, - note the '
TRUE
AND(04/01/2025<=12/31/2024,
FALSE
(-LOOKUP(04/01/2025,'Loan A - Amo'!$E$15:$E$314,'Loan A - Amo'!$S$15:$S$314)))), note the -
AS there is no False needed - all covered above - this will never run
IF(04/01/2025>12/31/2024,"-")


does that help at all - or have I just confused you a lot more ?

 

[ironman arms]

Yes, that does help a lot. I am closer lol.. So now it's pulling the end of year balances fine, and zeroing out "-" after the year of Re-Fi just fine.

But it's still not pulling the remaining balance from the 'Re-Fi Date Cell' out of the date range. It returns the balance at the end of the period (12/31). I am assuming my formula is still wrong though...

'Operating Budget'!$D$101 = RE-FI DATE (04/01/2025)
Z2 = 01/01/xx
Z6 = 12/31/xx

=IF(('Operating Budget'!$D$101>=Z2), (-LOOKUP(Z6,'Loan A - Amo'!$E$15:$E$314,'Loan A - Amo'!$S$15:$S$314)), (IF('Operating Budget'!$D$101>=Z2, AND('Operating Budget'!$D$101<=Z6, (-LOOKUP('Operating Budget'!$D$101,'Loan A - Amo'!$E$15:$E$314,'Loan A - Amo'!$S$15:$S$314))), "-")))

 

[etaf]

(IF('Operating Budget'!$D$101>=Z2, AND('Operating Budget'!$D$101<=Z6

what are you after here ?

this is your first test after the 1st IF and its the same here
Operating Budget'!$D$101>=Z2

so now if that is false
when it goes to

(IF('Operating Budget'!$D$101>=Z2, AND('Operating Budget'!$D$101<=Z6

that will also be false because it already failed the first test to get to this point anyway

so this test is wrong - also so is the layout
'Operating Budget'!$D$101>=Z2, AND('Operating Budget'!$D$101<=Z6
will NOT work - if you want to say both need to be TRUE (but take account of above
then you need
AND(Operating Budget'!$D$101>=Z2, 'Operating Budget'!$D$101<=Z6)
as the test
both need to be true to then use your lookup - but it wont be as the 1st test was already true as said above

 

[ironman arms]

I see what you're saying.. IF statements are really not my strong suit. Perhaps I should start w/ what I'm trying to accomplish.

I'm builidng a 15 year cash flow and trying to automate end of year loan balances. One of those years will end on the "Re-Fi Date" and I need that balance to be pulled using the lookup for that period.

So if the Re-fi date is 04/01/25.

01/01/24 - 12/31/24 = Loan Bal pulls as of: 12/31/24
01/01/25 - 12/31/25 = Loan Bal pulls fas of 04/01/25
01/01/26 - 12/31/26 = Needs to zero out from then on

So obviously not sure of the fomula format but essentially need a formula that will say...

IF( Re-Fi date is beyond the current period, Pull Loan Balance as if End of year 12/31),
IF( Re-Fi date occurs within the current period, Pull Balance as of "Re-Fi Date),
Following periods then need to be zeroed out... As it has been paid off.

I feel like an idiot, but this has got me stumped...

 

[etaf]

OK
so the two lookups you have do each one of these

01/01/24 - 12/31/24 = Loan Bal pulls as of: 12/31/24
01/01/25 - 12/31/25 = Loan Bal pulls fas of 04/01/25

=IF( Re-Fi > end of current period , do the lookup for that ,
IF( AND( Re-Fi > start of current period, Re-Fi < end of current period , do the look up,
0))

so we need the cell with the Re-Fi dates in = 'Operating Budget'!$D$101 = RE-FI DATE
the cell with the end of current period = Z2
the cell with the start of current period = Z6

=IF( 'Operating Budget'!$D$101 > Z2,
(LOOKUP(Z6,'Loan A - Amo'!$E$15:$E$314,'Loan A - Amo'!$S$15:$S$314)),
IF( AND( 'Operating Budget'!$D$101 <= Z2, 'Operating Budget'!$D$101 >= Z6) ,
(LOOKUP('Operating Budget'!$D$101,'Loan A - Amo'!$E$15:$E$314,'Loan A - Amo'!$S$15:$S$314)),
0))

should do it ???


so in long hand

is

'Operating Budget'!$D$101 greater than the cell with the end of current period Z2 - IF TRUE then do the 1st lookup
IF FALSE
then
'Operating Budget'!$D$101 less than or equal to the cell with the end of current period Z2
AND is it
'Operating Budget'!$D$101 greater or equal to the cell with the start of current period Z6

if the date is between those two dates-then it is TRUE and so then do the 2nd lookup
if the date is not between those two dates , and we have also tested that the date is greater than the end date so all that is left is it must be FALSE
so then we want to set to zero
0