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

 

[Scott Huish]

2 of those items has a duration of longer than a year. What kind of result are you looking to return?
If it was always a year, you could just do a COUNTIF

 

[MrHopeless]

2 of those items has a duration of longer than a year. What kind of result are you looking to return?
If it was always a year, you could just do a COUNTIF

The result would be the total duration of how long the occupant has lived in the unit in years and days.

 

[MrHopeless]

The result would be the total duration of how long the occupant has lived in the unit in years and days.

2 of those items has a duration of longer than a year. What kind of result are you looking to return?
If it was always a year, you could just do a COUNTIF

Years, months and days

 

[HongRu]

All the leases duration of each occupants are continuous ?
If true, the job will be easier.
We can get the duration by The last lease End minus the first lease Start.

 

[MrHopeless]

All the leases duration of each occupants are continuous ?
If true, the job will be easier.
We can get the duration by The last lease End minus the first lease Start.

Yes it is. I would like the formula to provide the duration for each prior occupant every time Col B shows a "New Lease". For example, when Jane Doe moved in with a "New Lease" on 9/9/2022, it will calculate the duration of time John Doe lived in the apartment from 12/10/2015-6/15/2021. But, make sure it doesn't track the first occupant Paula Doe and only does John Doe.

 

[HongRu]

Try to put the code below in the cell E5.
Maybe you should press "Ctrl+Shift+Enter" instead of "Enter".
Then copy downward to the last row.

I assume the data sort according to occupant and the first lease is always new one.

=IFERROR(DATEDIF(IF(A5<>A4,MIN(IF($A$5:$A$12=A5,$C$5:$C$12,7^7)),""),IF(A5<>A4,MAX(IF($A$5:$A$12=A5,$D$5:$D$12,0))+1,""),"Y") &"y"&DATEDIF(IF(A5<>A4,MIN(IF($A$5:$A$12=A5,$C$5:$C$12,7^7)),""),IF(A5<>A4,MAX(IF($A$5:$A$12=A5,$D$5:$D$12,0))+1,""),"YM")&"m"&DATEDIF(IF(A5<>A4,MIN(IF($A$5:$A$12=A5,$C$5:$C$12,7^7)),""),IF(A5<>A4,MAX(IF($A$5:$A$12=A5,$D$5:$D$12,0))+1,""),"MD")&"d","")

 

[MrHopeless]

Try to put the code below in the cell E5.
Maybe you should press "Ctrl+Shift+Enter" instead of "Enter".
Then copy downward to the last row.

I assume the data sort according to occupant and the first lease is always new one.

=IFERROR(DATEDIF(IF(A5<>A4,MIN(IF($A$5:$A$12=A5,$C$5:$C$12,7^7)),""),IF(A5<>A4,MAX(IF($A$5:$A$12=A5,$D$5:$D$12,0))+1,""),"Y") &"y"&DATEDIF(IF(A5<>A4,MIN(IF($A$5:$A$12=A5,$C$5:$C$12,7^7)),""),IF(A5<>A4,MAX(IF($A$5:$A$12=A5,$D$5:$D$12,0))+1,""),"YM")&"m"&DATEDIF(IF(A5<>A4,MIN(IF($A$5:$A$12=A5,$C$5:$C$12,7^7)),""),IF(A5<>A4,MAX(IF($A$5:$A$12=A5,$D$5:$D$12,0))+1,""),"MD")&"d","")

I appreciate the effort but it did not do what I was expecting. It calculated the duration from the first occupant to the last. It should be from the John Doe occupant and not including Paula Doe. The formula should be based on the type of lease and not the occupants. The reason being is that there could be multiple occupants and some would stay or leave on the next Lease Renewal.

If the the cell reads "New Lease", then figure out the duration of the prior tenant John Doe from 12/10/2015-6/15/2021 and not include Paula Doe. Or, I can work with a formula that provides the duration of the occupants from their "New Lease" to the next "New Lease".

Sorry if this is a pain.

 

[AhoyNC]

Try:

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 Months 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 Months 5 Days
12	Jane Doe	New Lease	9/9/2021	9/8/2022	0 Yr 11 Months 30 Days
Sheet1

Cell Formulas
Range	Formula
E5:E12	E5	=IF(A6=A5,"",DATEDIF(MIN(FILTER($C$5:$C$12,($A$5:$A$12=A5))),MAX(FILTER($D$5:$D$12,($A$5:$A$12=A5))),"y")&" Yr "&DATEDIF(MIN(FILTER($C$5:$C$12,($A$5:$A$12=A5))),MAX(FILTER($D$5:$D$12,($A$5:$A$12=A5))),"ym")&" Months "&DATEDIF(MIN(FILTER($C$5:$C$12,($A$5:$A$12=A5))),MAX(FILTER($D$5:$D$12,($A$5:$A$12=A5))),"md")&" Days ")

 

[MrHopeless]

Try:

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 Months 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 Months 5 Days
12	Jane Doe	New Lease	9/9/2021	9/8/2022	0 Yr 11 Months 30 Days
Sheet1

Cell Formulas
Range	Formula
E5:E12	E5	=IF(A6=A5,"",DATEDIF(MIN(FILTER($C$5:$C$12,($A$5:$A$12=A5))),MAX(FILTER($D$5:$D$12,($A$5:$A$12=A5))),"y")&" Yr "&DATEDIF(MIN(FILTER($C$5:$C$12,($A$5:$A$12=A5))),MAX(FILTER($D$5:$D$12,($A$5:$A$12=A5))),"ym")&" Months "&DATEDIF(MIN(FILTER($C$5:$C$12,($A$5:$A$12=A5))),MAX(FILTER($D$5:$D$12,($A$5:$A$12=A5))),"md")&" Days ")

Sh*t my work computer only has Excel 2016 which doesn't have the FILTER formula. Your formula looks spot on but of course I can't use it at my job (which is where I need it). I assume there is no alternative to this formula without the "FILTER" formula?