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

 

[KRice]

I reviewed post #1 and the formula for col AA in post #6 and noted a discrepancy. Post #1 calls for the value in column R to be returned, while Post #6 shows the value in column Q being returned. The Post #6 commentary, however, does not clear up much for me. I inserted the formula I suggested earlier and revised the cell references. If we take the formula in AA47 as an example, it does the following, in this order:

1. Examine cell Q47: If (Q47 is blank), then return a blank.
2. Examine cells R47 and Y47: If (R47 is not blank) AND (Y47 is not 0), then return Q47.
3. Examine cells O47, Q47, and Y47: If (O47 is either 12 or 24) AND (Q47 is not blank) AND (Y47 is 0), then return Q47.
4. If none of the preceding conditions are met, then a blank is returned.

If during these evaluations the conditional expression evaluates to TRUE, the corresponding action is taken and any remaining evaluations are not performed. Please feel free to add/delete/edit to clarify what the expected results are.

MrExcel_20220426.xlsm
	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	contract_length	starts_at	ends_at	Ends EOMONTH	1/31/2022	Check	Quarter			Renewal Date 1	End Date Check	Renewal Date	End Date	End Date 1	2022	1	31
47	12	2/22/2018	3/21/2021	3/31/2021	47	48	48	36	48	2/28/2022	11	2/28/2022	3/21/2021	3/31/2021
88	12	2/1/2018			47	48	48	36	48	2/28/2022	0	2/28/2022
89	12	2/8/2018			47	48	48	36	48	2/28/2022	0	2/28/2022
90	12	2/23/2018			47	48	48	36	48	2/28/2022	0	2/28/2022
Sheet11

Cell Formulas
Range	Formula
S1	S1	=DATE(AC1,AD1,AE1)
R47,R88:R90	R47	=IF(Q47<1/1/2020,"",EOMONTH(Q47,0))
S47,S88:S90	S47	=DATEDIF(P47,$S$1,"M")
T47,T88:T90	T47	=IFS(O47=3,U47,O47=6,U47,O47=9,U47,O47=12,W47,O47=24,V47,O47=18,18,O47=1,S47)
U47,U88:U90	U47	=ROUNDUP(S47/O47,0)*O47
V47,V88:V90	V47	=IFS(S47<24,24,S47>24,36,S47<37,36,S47>36,48,S47<73,72)
W47,W88:W90	W47	=ROUNDUP(S47/12,0)*12
X47,X88:X90	X47	=EOMONTH(P47,T47)
Y47,Y88:Y90	Y47	=IFERROR(IF(Q47<> "",DATEDIF(Q47,X47,"M"),0),0)
Z47	Z47	=IF(Y47>0,X47,Q47)
AB47,AB88:AB90	AB47	=IFERROR(EOMONTH(AA47,0),"")
AA47,AA88:AA90	AA47	=IF(Q47="", "", IF(AND(R47<>"",Y47<>0), Q47, IF(AND(Q47<>"",Y47=0,OR(O47=12,O47=24)), Q47, "" ) ) )
Z88:Z90	Z88	=IF(Y88<12,X88,Q88)

 

[KRice]

Also please check the Q47 condition carefully. To return a value, do you want one of the conditions to be that Q47 IS or IS NOT blank? I'm not clear on this, as I see contradictory expressions: In Post #1 I see AND(Q1<>"" while in Post #6 I see ISBLANK(Q47), which is the opposite.

 

[Nataliea]

Normally I am in favour of splitting formulas down into smaller understandable steps but you already have so many columns and its not clear what they are trying to achieve.

The renewal date seems to be the next renewal date on or after the current month (given in a cell).
There are references to 12 & 24 month terms but are they the only options and is the rule the same for all the terms ie the next renewal date is always the next multiple of the term after the current month ?

Calculating a new end date.
The options seem to be:
a) End date is blank
then new end date is blank
b) End date is less than the calculated next renewal date
then ???
c) End date is greater than the calculated next renewal date
then ???
d) End date = calculated renewal date
then ???

Can you fill in the blanks (???) and let clarify if I have misunderstood it.
PS: I will login off for the night and won't get a chance to look at it again until tomorrow.

Renewals are based on 3,6,9,12 and 24 month contract terms

Calculating a new end date.
The options seem to be:
a) End date is blank
then new end date is blank
b) End date is less than the calculated next renewal date
then ??? - new end date is same as next renewal date
c) End date is greater than the calculated next renewal date
then ??? Then end date would be next calculated renewal date after this date
d) End date = calculated renewal date
then end date is the calculated renewal date

Thank you ?

 

[KRice]

I need some help to understand your terminology. Would you please add to your last post which columns are referred to where you mention: "end date", "new end date", "calculated new renewal date", "next renewal date", "next calculated renewal date", "calculated next renewal date", "calculated renewal date"?

I can't follow what is needed or whether some of these terms refer to the same cells.

 

[Nataliea]

I need some help to understand your terminology. Would you please add to your last post which columns are referred to where you mention: "end date", "new end date", "calculated new renewal date", "next renewal date", "next calculated renewal date", "calculated next renewal date", "calculated renewal date"?

I can't follow what is needed or whether some of these terms refer to the same cells.

Q - "end date",
AA -"new end date",
X -"calculated new renewal date",
"next renewal date",
X- "next calculated renewal date",
X -"calculated next renewal date",
X -"calculated renewal date"?

Sorry, I think I have overcomplicated everything by adding columns with formulas in that I was using to workout renewal date based on the term. The data export has a renewal date column but the devs code over writes it with the end date when a contract is ended which has caused my issue as I would have just used the renewal date as the end date.

 

[KRice]

Thank you. I'll have a look at this in a little while. I have to step away right now.

 

[Nataliea]

I have relooked at it and I definitely was overcomplicating it. I have added a column with this formula
=IF(Q2<X2,X2,Q2) and deleted all columns after Column X and it seems to do what I need

 

[KRice]

In Post #13:

c) End date is greater than the calculated next renewal date
then ??? Then end date would be next calculated renewal date after this date

Could you explain this "next calculated renewal date" a little more, please? It doesn't make sense that this would be referring to column X. Based on your latest Post #17, it looks as if you mean column Q? Did the latest change you described in Post #17 resolve your issue entirely, or does something else need attention?

 

[KRice]

I was looking at the column V formula and have a few comments:

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

The original IFS function might be a problem because of the order of the logical tests. The 1st TRUE will be honored, so S=40 should probably return 48, but the formula as written would return 36. The formula for V consists of many logical expressions, and this can probably be simplified. It appears that there are four tiers that S might fall into (0-24), (24-36), (36-72) and (72+). What should happen where these tiers meet (if S equals 24, 36, or 72 exactly). The current formula is written in terms of > and < with overlapping ranges. Rewriting this expression so that there is no overlap and having well defined range end points would be clearer...and one end of each range would be written in terms of <= or >= depending on your requirements.

 

[Alex Blakenburg]

I have relooked at it and I definitely was overcomplicating it. I have added a column with this formula
=IF(Q2<X2,X2,Q2) and deleted all columns after Column X and it seems to do what I need

I will assume from this that you now have it sorted. Please let us know if that is not the case and you still need us to do something.