ISBLANK <>"" not returning right result

[Nataliea]

I have a spreadsheet which holds contract data i need to check if a contract has ended part way through the contract term and amend the end date to renewal date but also return nothing if the contract has no end date

This is the formula i was using
AA=IF(AND(Q1<>"",Y1=0 O1=12) R1,IF(AND(Q1<>"",Y1=0,O1=24) R1 ""))

If Q is not blank and contract has ended at renewal date and it is a 12 or 24 mth contract enter end date from cell R

Where Y is DATEDIF formula to check months between end date and next renewal, R is end date and O is contract term.
So if contract ended on 31/03/2021 but started on the 38/02/2020 so should have ran for another 11 months new end date should be 38/02/2022. If Q is empty then formula returns a blank cell (empty)
I also need it to be able to get it to return cell Z if end date is not blank but term has not ended.

I'm sure there must be an easier/ better formula but I'm stumped.

Can you help

 

[Nataliea]

Thank you I have now resolved this, but will look at my IFS ordering @KRice . @Alex Blakenburg thank you for your help

 

[Alex Blakenburg]

Thanks for the feedback. Glad we could help.

 

[KRice]

When you do look at the IFS in column V, you might consider something like

=XLOOKUP(1,S96<={0,24,36,72},{24,36,48,72},"",1,-1)

rather than something like...

=IFS(S90<24,24,S90>24,36,S90<37,36,S90>36,48,S90<73,72)

The first steps through the intervals to determine which one the S value lies within and then returns the corresponding value in the other array, but without answers to the questions I asked, I can't advise on specifics in the formula, as they may need to be changed.