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

 

[Alex Blakenburg]

This will be much easier and you are more likely to get input if you provide an XL2BB with some sample data and with sample results.
Your sample formula uses row 1 which I would normally expect to hold the headings.

I am unclear as to as to how to calculate "end date is not blank but term has not ended." in which case you want Z1 returned.
Let's start with this and when you provide some data we can fine tune it.

=IF(Q2="","",IF(AND(Y2=0,OR(O2=12,O2=24)),R2,"What are the altenatives"))

Using XL2BB​

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[KRice]

I agree with Alex's point. Without more detail about your worksheet structure and the contents of some cells that are mentioned, I can only guess. Here, in AA1 are three nested IF statements that I believe address the three different comments in post #1 quoted below (although the cell references could be incorrect, as they are not described).

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

Note that the order of the nested IFs matters, as the first one that evaluates to TRUE will determine the action taken by the formula, so you may need to order them differently. And, of course, correct cell references where I've guessed incorrectly.

MrExcel_20220426.xlsm
	N	O	P	Q	R							Y	Z	AA
1	7/31/2020	24		1	7/31/2022							3	something	something
2	guess start date	contract term (m)		blank or not	end date							guess month's remaining	something here	result
Sheet8

Cell Formulas
Range	Formula
R1	R1	=EDATE(N1,O1)
Y1	Y1	=DATEDIF(TODAY(),R1,"m")
AA1	AA1	=IF(Q1="", "", IF(AND(R1<>"",Y1<>0), Z1, IF(AND(Q1<>"",Y1=0,OR(O1=12,O1=24)), R1, "" ) ) )

 

[Nataliea]

This will be much easier and you are more likely to get input if you provide an XL2BB with some sample data and with sample results.
Your sample formula uses row 1 which I would normally expect to hold the headings.

I am unclear as to as to how to calculate "end date is not blank but term has not ended." in which case you want Z1 returned.
Let's start with this and when you provide some data we can fine tune it.

=IF(Q2="","",IF(AND(Y2=0,OR(O2=12,O2=24)),R2,"What are the altenatives"))

Using XL2BB​

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thanks I will use the tool and post a sample

 

[Nataliea]

I agree with Alex's point. Without more detail about your worksheet structure and the contents of some cells that are mentioned, I can only guess. Here, in AA1 are three nested IF statements that I believe address the three different comments in post #1 quoted below (although the cell references could be incorrect, as they are not described).

Note that the order of the nested IFs matters, as the first one that evaluates to TRUE will determine the action taken by the formula, so you may need to order them differently. And, of course, correct cell references where I've guessed incorrectly.

MrExcel_20220426.xlsm
	N	O	P	Q	R							Y	Z	AA
1	7/31/2020	24		1	7/31/2022							3	something	something
2	guess start date	contract term (m)		blank or not	end date							guess month's remaining	something here	result
Sheet8

Cell Formulas
Range	Formula
R1	R1	=EDATE(N1,O1)
Y1	Y1	=DATEDIF(TODAY(),R1,"m")
AA1	AA1	=IF(Q1="", "", IF(AND(R1<>"",Y1<>0), Z1, IF(AND(Q1<>"",Y1=0,OR(O1=12,O1=24)), R1, "" ) ) )

Row 1 does have my headings that should have said 2 as row reference

 

[Nataliea]

Renewals 2022 - 2023 test.xlsx
	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	contract_length	starts_at	ends_at	Ends EOMONTH	31/01/2022	Check	Quarter			Renewal Date 1	End Date Check	Renewal Date	End Date	End Date 1
47	12	22/02/2018	21/03/2021	31/03/2021	47	48	48	36	48	28/02/2022	11	28/02/2022	21/03/2021	31/03/2021
88	12	01/02/2018			47	48	48	36	48	28/02/2022	0	28/02/2022
89	12	08/02/2018			47	48	48	36	48	28/02/2022	0	28/02/2022
90	12	23/02/2018			47	48	48	36	48	28/02/2022	0	28/02/2022
salesfire-packages-export-2022-

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)
AA47	AA47	=IF(AND(Y47=0,O47=12,ISBLANK(Q47)),Q47,IF(AND(Y47=0,O47=24,ISBLANK(Q47)),"",Q47))
AB47,AB88:AB90	AB47	=IFERROR(EOMONTH(AA47,0),"")
Z88:Z90	Z88	=IF(Y88<12,X88,Q88)
AA88:AA90	AA88	=IF(AND(Q88<>"",Y88=0,O88=12),Q88,IF(AND(Q88<>"",Y88=0,O88=24),Q88,""))

 

[Nataliea]

All cell ranges between B and Q are extracted from the system. Hope the minisheet gives enough information.

 

[Alex Blakenburg]

Unfortunately I still can't work out the rest of the scenarios eg
a) For Z how do we determine that the term date has not ended (given that the end date is not blank) ie which column and what criteria.
b) Is your example of 28/02/2022 & 11 months another combination ?

In other words give us an individual If statement for each of the other combinations not already covered then we can help you string them together into the one If statement to cover all the different combinations.

20220429 Contract Expiry If Statement Nataliea.xlsx
	P	Q	R	S	T	U	V	W
97	Column	Name	Details	Combination 1	Combination 2	Combination 3	Combination 4	Combination 5
98	Q	ends_at	Manual Entry	BLANK	not blank	not blank
99	Y	End Date Check	Renewal Date 1 > End Date then Months otherwise 0		0
100	O	contract_length	eg 12,24		=12 or 24
101	???	Term Date not Ended				???
102
103
104	End Date			BLANK	R - (Eomonth of End Date)	Z - (Renewal Date)
OP Data

 

[Nataliea]

Sorry I should have said all contracts are rolling term so for example a 12 month contract which is ended after the last renewal date needs to be recorded in the records as ending at the next renewal date.
Column P = is fixed figure from the data export and never changes. This is the date the contract started originally.
Column U= I was using this to look at Column T and round up based on the term, so if number of months between start date and current month is 47 but term is 12 months it rounds to 48
Column S= looks at months since contract started based on the end of month added into my date cell (I'm using S1 but this should be outside of the table and I need to move it)

The data is being pulled into a table which shows MRR of packages due to renew in the month with another table which shows MRR lost as the package ended or should have ended in the month based on renewal term.

I have the first table working and can pull based on contract end date being same month that contract was due to renew but can't work out factoring in contracts with an end date between last renewal and next renewal date. Would it make more sense to add a column that calculates a new end date by adding the additional months (if any) and the end formula just uses this column? I did Excel at Uni but that was over 20 years ago and I'm a bit rusty.

Thanks in advance

 

[Alex Blakenburg]

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.