# Formula Creation Help



## MrHopeless (Dec 22, 2022)

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 (Dec 22, 2022)

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 (Dec 22, 2022)

Scott Huish said:


> 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 (Dec 22, 2022)

MrHopeless said:


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





Scott Huish said:


> 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 (Dec 23, 2022)

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 (Dec 23, 2022)

HongRu said:


> 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 (Dec 23, 2022)

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 (Dec 23, 2022)

HongRu said:


> 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 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 (Dec 23, 2022)

Try:
Book1ABCDE123OccupantType of leaseLease StartLease End45Paula DoeNew Lease12/10/201212/9/2013 6Paula DoeLease Renewal12/10/201312/9/2014 7Paula DoeLease Renewal12/10/201412/9/20152 Yr 11 Months 29 Days8John DoeNew Lease12/10/201512/9/2016 9John DoeLease Renewal12/10/201612/9/2017 10John DoeLease Renewal12/10/201712/9/2019 11John DoeLease Renewal3/16/20206/15/20215 Yr 6 Months 5 Days12Jane DoeNew Lease9/9/20219/8/20220 Yr 11 Months 30 DaysSheet1Cell FormulasRangeFormulaE5:E12E5=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 (Dec 23, 2022)

AhoyNC said:


> Try:
> Book1ABCDE123OccupantType of leaseLease StartLease End45Paula DoeNew Lease12/10/201212/9/2013 6Paula DoeLease Renewal12/10/201312/9/2014 7Paula DoeLease Renewal12/10/201412/9/20152 Yr 11 Months 29 Days8John DoeNew Lease12/10/201512/9/2016 9John DoeLease Renewal12/10/201612/9/2017 10John DoeLease Renewal12/10/201712/9/2019 11John DoeLease Renewal3/16/20206/15/20215 Yr 6 Months 5 Days12Jane DoeNew Lease9/9/20219/8/20220 Yr 11 Months 30 DaysSheet1Cell FormulasRangeFormulaE5:E12E5=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?


----------



## MrHopeless (Dec 22, 2022)

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 (Dec 23, 2022)

Maybe this:
Book1ABCDE123OccupantType of leaseLease StartLease End45Paula DoeNew Lease12/10/201212/9/2013 6Paula DoeLease Renewal12/10/201312/9/2014 7Paula DoeLease Renewal12/10/201412/9/20152 Yr  11 month  29 days8John DoeNew Lease12/10/201512/9/2016 9John DoeLease Renewal12/10/201612/9/2017 10John DoeLease Renewal12/10/201712/9/2019 11John DoeLease Renewal3/16/20206/15/20215 Yr  6 month  5 days12Jane DoeNew Lease9/9/20219/8/20220 Yr  11 month  30 daysSheet1Cell FormulasRangeFormulaE5:E12E5=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 (Dec 23, 2022)

AhoyNC said:


> Maybe this:
> Book1ABCDE123OccupantType of leaseLease StartLease End45Paula DoeNew Lease12/10/201212/9/2013 6Paula DoeLease Renewal12/10/201312/9/2014 7Paula DoeLease Renewal12/10/201412/9/20152 Yr  11 month  29 days8John DoeNew Lease12/10/201512/9/2016 9John DoeLease Renewal12/10/201612/9/2017 10John DoeLease Renewal12/10/201712/9/2019 11John DoeLease Renewal3/16/20206/15/20215 Yr  6 month  5 days12Jane DoeNew Lease9/9/20219/8/20220 Yr  11 month  30 daysSheet1Cell FormulasRangeFormulaE5:E12E5=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 (Dec 23, 2022)

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

Book1ABCDE123OccupantType of leaseLease StartLease End45Paula DoeNew Lease12/10/201212/9/2013 6Paula DoeLease Renewal12/10/201312/9/2014 7Paula DoeLease Renewal12/10/201412/9/20152 Yr  11 month  29 days8John DoeNew Lease12/10/201512/9/2016 9John DoeLease Renewal12/10/201612/9/2017 10John DoeLease Renewal12/10/201712/9/2019 11John DoeLease Renewal3/16/20206/15/20215 Yr  6 month  5 days12Jane DoeNew Lease9/9/20219/8/20220 Yr  11 month  30 daysSheet1Cell FormulasRangeFormulaE5:E12E5=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 (Dec 23, 2022)

AhoyNC said:


> This is an array formula that needs to entered with CTRL-SHIFT-ENTER. Then drag down as needed.
> 
> Book1ABCDE123OccupantType of leaseLease StartLease End45Paula DoeNew Lease12/10/201212/9/2013 6Paula DoeLease Renewal12/10/201312/9/2014 7Paula DoeLease Renewal12/10/201412/9/20152 Yr  11 month  29 days8John DoeNew Lease12/10/201512/9/2016 9John DoeLease Renewal12/10/201612/9/2017 10John DoeLease Renewal12/10/201712/9/2019 11John DoeLease Renewal3/16/20206/15/20215 Yr  6 month  5 days12Jane DoeNew Lease9/9/20219/8/20220 Yr  11 month  30 daysSheet1Cell FormulasRangeFormulaE5:E12E5=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 (Dec 23, 2022)

You're welcome. Glad we could help.


----------

