To shorten a lengthy formula

[SanjayaGarg]

I made a very simple formula in column P (Tenure Completed on) , but the formula is very lengthy as it covers from column V to column AB. Can this be shortened with same function. Formula is as under :

'=IF(N2="","",IF(V2="",O2,IF(AND(W2="",K2="year"),DATE(YEAR(V2)+J2,MONTH(V2),DAY(V2)-1),IF(AND(W2="",K2="months"),DATE(YEAR(V2),MONTH(V2)+J2,DAY(V2)-1),IF(AND(W2="",K2="days"),DATE(YEAR(V2),MONTH(V2),DAY(V2)+J2-),IF(AND(X2="",K2="year"),DATE(YEAR(W2)+J2,MONTH(W2),DAY(W2)-1),IF(AND(X2="",K2="months"),DATE(YEAR(W2),MONTH(W2)+J2,DAY(W2)-1),IF(AND(X2="",K2="days"),DATE(YEAR(W2),MONTH(W2),DAY(W2)+J2-),IF(AND(Y2="",K2="year"),DATE(YEAR(X2)+J2,MONTH(X2),DAY(X2)-1),IF(AND(Y2="",K2="months"),DATE(YEAR(X2),MONTH(X2)+J2,DAY(X2)+J2-),IF(AND(Y2="",K2="days"),DATE(YEAR(X2),MONTH(X2),DAY(X2)-1),IF(AND(Z2="",K2="year"),DATE(YEAR(Y2)+J2,MONTH(Y2),DAY(Y2)-1),IF(AND(Z2="",K2="months"),DATE(YEAR(Y2),MONTH(Y2)+J2,DAY(Y2)-1),IF(AND(Z2="",K2="days"),DATE(YEAR(Y2),MONTH(Y2),DAY(Y2)+J2-),IF(AND(AA2="",K2="year"),DATE(YEAR(Z2)+J2,MONTH(Z2),DAY(Z2)-1),IF(AND(AA2="",K2="months"),DATE(YEAR(Z2),MONTH(Z2)+J2,DAY(Z2)-1),IF(AND(AA2="",K2="days"),DATE(YEAR(Z2),MONTH(Z2),DAY(Z2)+J2-1),"")))))))))))))))))

Table is as under :

J

K

L

M​

N​

O​

P​

Q​

R​

S​

T​

U​

V​

W​

X​

Y​

Z​

AA​

AB​

1

Tenure

Period

Joining Date​

Initial tenure completed on​

Tenure Completed on​

Extn 1​

Extn 2​

Extn 3​

Extn 4​

Extn 5​

Extn 6​

Extn 7​

2​

44​

days​

13-03-2020​

25-04-2020​

23-10-2021​

14-05-2020​

27-06-2020​

10-08-2020​

23-09-2020​

06-11-2020​

20-12-2020​

02-02-2021​

3​

5​

year​

13-03-2020​

25-04-2020​

23-10-2021​

14-05-2020​

27-06-2020​

10-08-2020​

23-09-2020​

06-11-2020​

20-12-2020​

02-02-2021​

4​

3​

months​

13-03-2020​

25-04-2020​

23-10-2021​

14-05-2020​

27-06-2020​

10-08-2020​

23-09-2020​

06-11-2020​

20-12-2020​

02-02-2021​

 

[jasonb75]

I doubt that anyone would want to pick that broken formula apart when you could quite easily tell is in simple terms what it should be doing.

 

[Eric W]

How about:

Book1
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Tenure	Period			Joining Date	Initial Tenure completed on	Tenure Completed on						Extn 1	Extn 2	Extn 3	Extn 4	Extn 5	Extn 6	Extn 7
2	44	days			3/13/2020	4/25/2020	2/1/2021						5/14/2020	6/27/2020	8/10/2020	9/23/2020	11/6/2020	12/20/2020
3	5	year			3/13/2020	4/25/2020	11/5/2025						5/14/2020	6/27/2020	8/10/2020	9/23/2020	11/6/2020
4	3	months			3/13/2020	4/25/2020	11/9/2020						5/14/2020	6/27/2020	8/10/2020
Sheet1

Cell Formulas
Range	Formula
P2:P4	P2	=IF(N2="","",IF(V2="",O2,IF(K2="days",LOOKUP(9^9,V2:AB2)+J2,EDATE(LOOKUP(9^9,V2:AB2),J2*IF(K2="Year",12,1)))-1))

 

[SanjayaGarg]

Thanks Eric. You provided me exactly what I wanted. Can you please explain functioning of this formula e.g. what is meant by 9^9 and how it is calculating if there is 'months' in K2 coz you have not mentioned months in the formula. Thanks a lot again for saving my day.

 

[jasonb75]

not mentioned months in the formula.

The formula doesn't need to check months. Days and Years are checked, if K2 is neither of those then it must be months.

what is meant by 9^9

That is a quick way of writing 387420489. When looking for the last date in a row / column you just need to look for a number that is greater than the largest possible date serial. In reality, a number around 50000 is adequate for current dates but it is common to go much larger to prevent future errors.

 

[SanjayaGarg]

The formula doesn't need to check months. Days and Years are checked, if K2 is neither of those then it must be months.

That is a quick way of writing 387420489. When looking for the last date in a row / column you just need to look for a number that is greater than the largest possible date serial. In reality, a number around 50000 is adequate for current dates but it is common to go much larger to prevent future errors.

OK. Thanks a lot for solution & explaining it nicely. Thanks once again

 

[jasonb75]

You're welcome!

Although you should really mark @Eric W's post as the solution, mine was only an explanation of his formula that you asked for.

 

[Eric W]

Using LOOKUP and a number bigger than any number in the range is a trick to get the rightmost value in the range. And as Jason explained, 9^9 is a quick way to write a very big number. But I noticed that all your dates in that range are in increasing order. If so, then the rightmost date will also be the maximum. Then we can shorten the formula a bit:

=IF(N2="","",IF(V2="",O2,IF(K2="days",MAX(V2:AB2)+J2,EDATE(MAX(V2:AB2),J2*IF(K2="Year",12,1)))-1))

Glad we could help!