Formulas to help work out the number of days a year our holiday homes was rented out with a problem over two years in one hire?s

[tonywatsonhelp]

Hi Everyone,

I'm trying to find a way (formulas or VBA but formulas preferred I can work out how many days per year I rented out a holiday home,
I've simplified it by just showing a small amount of data but hopefully you will see my problems.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[TD]Q
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]

Lease Start Date (dd/mm/yyyy)​

<strike></strike>
[/TD]
[TD]<strike></strike>Lease End Date
[/TD]
[TD][/TD]
[TD]Number of days rented (i'm just using the formula =C3-B3 for this.)

[/TD]
[TD]2012
[/TD]
[TD]2013
[/TD]
[TD]2014
[/TD]
[TD]2015
[/TD]
[TD]2016
[/TD]
[TD]2017
[/TD]
[TD]2018
[/TD]
[TD]2019
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD]01/02/2012
[/TD]
[TD]15/07/2012
[/TD]
[TD][/TD]
[TD]165
[/TD]
[TD]so because both start and end date is in 2012 I just need the formula to put "165" in here.
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD]15/07/2013
[/TD]
[TD]15/3/2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD]but heres my big problem, if the lease date goes between two years how do I split it up???
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]
[/TD]
[TD]Below is the result I'd like to see
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD]

Lease Start Date (dd/mm/yyyy)​

<strike></strike>
[/TD]
[TD]

Lease End Date​

[/TD]
[TD][/TD]
[TD]Number of Days Rented
[/TD]
[TD]2012
[/TD]
[TD]2013
[/TD]
[TD]2014
[/TD]
[TD]2015
[/TD]
[TD]2016
[/TD]
[TD]2017
[/TD]
[TD]2018
[/TD]
[TD]2019
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD]1/2/2012
[/TD]
[TD]1/6/2012
[/TD]
[TD][/TD]
[TD]121
[/TD]
[TD]121
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD]1/5/2018
[/TD]
[TD]2/5/2019
[/TD]
[TD][/TD]
[TD]366
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]244
[/TD]
[TD]122
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD]1/4/2016
[/TD]
[TD]1/8/2016
[/TD]
[TD][/TD]
[TD]122
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]122
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD]1/8/2016
[/TD]
[TD]1/3/2017
[/TD]
[TD][/TD]
[TD]212
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD]152
[/TD]
[TD]60
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD][/TD]
[TD]1/11/2013
[/TD]
[TD]1/2/2014
[/TD]
[TD][/TD]
[TD]92
[/TD]
[TD][/TD]
[TD]60
[/TD]
[TD]32
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So hope the chart helps, its the results in red I'm having trouble getting.
please help if you can

Tony

 

[VBA Geek]

Try this:

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=MAX(<font color="Blue">0,MIN(<font color="Red">$B2,1+DATE(<font color="Green">D$1,12,31</font>)</font>)-MAX(<font color="Red">$A2,DATE(<font color="Green">D$1,1,1</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	Lease Start Date (dd/mm/yyyy)	Lease End Date	Number of days rented	2012	2013	2014	2015	2016	2017	2018	2019
2	1-Feb-12	15-Jul-12	165	165	0	0	0	0	0	0	0
3	15-Jul-13	15-Mar-17	1339	0	170	365	365	366	73	0	0
Sheet2

 

[tonywatsonhelp]

Thank you VBA Geek,

this works perfectly

Tony