Formula Creation Help

[MrHopeless]

Hello- I need help with trying to get a formula to do the following:

If(B12 = "New Lease", figure out the duration for Occupant John Doe from C8 to D11). However, I do not want it to capture the Occupant, Paula Doe. Or, I am ok with doing a separate column to calculate the duration of the Occupants and plugging those results into my other formula.

Thank you

 

[AhoyNC]

Maybe this:

Book1
	A	B	C	D	E
1
2
3	Occupant	Type of lease	Lease Start	Lease End
4
5	Paula Doe	New Lease	12/10/2012	12/9/2013
6	Paula Doe	Lease Renewal	12/10/2013	12/9/2014
7	Paula Doe	Lease Renewal	12/10/2014	12/9/2015	2 Yr 11 month 29 days
8	John Doe	New Lease	12/10/2015	12/9/2016
9	John Doe	Lease Renewal	12/10/2016	12/9/2017
10	John Doe	Lease Renewal	12/10/2017	12/9/2019
11	John Doe	Lease Renewal	3/16/2020	6/15/2021	5 Yr 6 month 5 days
12	Jane Doe	New Lease	9/9/2021	9/8/2022	0 Yr 11 month 30 days
Sheet1

Cell Formulas
Range	Formula
E5:E12	E5	=IF(A6=A5,"",DATEDIF(MINIFS($C$5:$C$12,$A$5:$A$12,A5),MAXIFS($D$5:$D$12,$A$5:$A$12,A5),"y")&" Yr "&DATEDIF(MINIFS($C$5:$C$12,$A$5:$A$12,A5),MAXIFS($D$5:$D$12,$A$5:$A$12,A5),"ym")&" month "&DATEDIF(MINIFS($C$5:$C$12,$A$5:$A$12,A5),MAXIFS($D$5:$D$12,$A$5:$A$12,A5),"md")&" days ")

 

[MrHopeless]

Maybe this:

Book1
	A	B	C	D	E
1
2
3	Occupant	Type of lease	Lease Start	Lease End
4
5	Paula Doe	New Lease	12/10/2012	12/9/2013
6	Paula Doe	Lease Renewal	12/10/2013	12/9/2014
7	Paula Doe	Lease Renewal	12/10/2014	12/9/2015	2 Yr 11 month 29 days
8	John Doe	New Lease	12/10/2015	12/9/2016
9	John Doe	Lease Renewal	12/10/2016	12/9/2017
10	John Doe	Lease Renewal	12/10/2017	12/9/2019
11	John Doe	Lease Renewal	3/16/2020	6/15/2021	5 Yr 6 month 5 days
12	Jane Doe	New Lease	9/9/2021	9/8/2022	0 Yr 11 month 30 days
Sheet1

Cell Formulas
Range	Formula
E5:E12	E5	=IF(A6=A5,"",DATEDIF(MINIFS($C$5:$C$12,$A$5:$A$12,A5),MAXIFS($D$5:$D$12,$A$5:$A$12,A5),"y")&" Yr "&DATEDIF(MINIFS($C$5:$C$12,$A$5:$A$12,A5),MAXIFS($D$5:$D$12,$A$5:$A$12,A5),"ym")&" month "&DATEDIF(MINIFS($C$5:$C$12,$A$5:$A$12,A5),MAXIFS($D$5:$D$12,$A$5:$A$12,A5),"md")&" days ")

I am sorry my job is cheap. We only have Office Professional 2016. The MINIFS and MAXIFS is only form 2019 to Present. **** I feel ashamed. Makes me want to buy the new versions for them. Another solution? Maybe Index and Aggregate? I heard it might be similar.

 

[AhoyNC]

This is an array formula that needs to entered with CTRL-SHIFT-ENTER. Then drag down as needed.

Book1
	A	B	C	D	E
1
2
3	Occupant	Type of lease	Lease Start	Lease End
4
5	Paula Doe	New Lease	12/10/2012	12/9/2013
6	Paula Doe	Lease Renewal	12/10/2013	12/9/2014
7	Paula Doe	Lease Renewal	12/10/2014	12/9/2015	2 Yr 11 month 29 days
8	John Doe	New Lease	12/10/2015	12/9/2016
9	John Doe	Lease Renewal	12/10/2016	12/9/2017
10	John Doe	Lease Renewal	12/10/2017	12/9/2019
11	John Doe	Lease Renewal	3/16/2020	6/15/2021	5 Yr 6 month 5 days
12	Jane Doe	New Lease	9/9/2021	9/8/2022	0 Yr 11 month 30 days
Sheet1

Cell Formulas
Range	Formula
E5:E12	E5	=IF(A6=A5,"",DATEDIF(MIN(IF($A$5:$A$12=A5,$C$5:$C$12)),MAX(IF($A$5:$A$12=A5,$D$5:$D$12)),"y")&" Yr "&DATEDIF(MIN(IF($A$5:$A$12=A5,$C$5:$C$12)),MAX(IF($A$5:$A$12=A5,$D$5:$D$12)),"ym")&" month "&DATEDIF(MIN(IF($A$5:$A$12=A5,$C$5:$C$12)),MAX(IF($A$5:$A$12=A5,$D$5:$D$12)),"md")&" days ")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MrHopeless]

This is an array formula that needs to entered with CTRL-SHIFT-ENTER. Then drag down as needed.

Book1
	A	B	C	D	E
1
2
3	Occupant	Type of lease	Lease Start	Lease End
4
5	Paula Doe	New Lease	12/10/2012	12/9/2013
6	Paula Doe	Lease Renewal	12/10/2013	12/9/2014
7	Paula Doe	Lease Renewal	12/10/2014	12/9/2015	2 Yr 11 month 29 days
8	John Doe	New Lease	12/10/2015	12/9/2016
9	John Doe	Lease Renewal	12/10/2016	12/9/2017
10	John Doe	Lease Renewal	12/10/2017	12/9/2019
11	John Doe	Lease Renewal	3/16/2020	6/15/2021	5 Yr 6 month 5 days
12	Jane Doe	New Lease	9/9/2021	9/8/2022	0 Yr 11 month 30 days
Sheet1

Cell Formulas
Range	Formula
E5:E12	E5	=IF(A6=A5,"",DATEDIF(MIN(IF($A$5:$A$12=A5,$C$5:$C$12)),MAX(IF($A$5:$A$12=A5,$D$5:$D$12)),"y")&" Yr "&DATEDIF(MIN(IF($A$5:$A$12=A5,$C$5:$C$12)),MAX(IF($A$5:$A$12=A5,$D$5:$D$12)),"ym")&" month "&DATEDIF(MIN(IF($A$5:$A$12=A5,$C$5:$C$12)),MAX(IF($A$5:$A$12=A5,$D$5:$D$12)),"md")&" days ")
Press CTRL+SHIFT+ENTER to enter array formulas.

You, @HongRu and a little help from @Scott Huish just made my life much more easier. Thank you all.

 

[AhoyNC]

You're welcome. Glad we could help.