SanjayaGarg
New Member
- Joined
- Nov 10, 2017
- Messages
- 25
- Office Version
- 2013
- Platform
- Windows
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 :
'=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 |