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.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2		Lease Start Date (dd/mm/yyyy)​ <strike></strike>	<strike></strike>Lease End Date		Number of days rented (i'm just using the formula =C3-B3 for this.)	2012	2013	2014	2015	2016	2017	2018	2019
3		01/02/2012	15/07/2012		165	so because both start and end date is in 2012 I just need the formula to put "165" in here.
4		15/07/2013	15/3/2014			but heres my big problem, if the lease date goes between two years how do I split it up???
5		Below is the result I'd like to see
6		Lease Start Date (dd/mm/yyyy)​ <strike></strike>	Lease End Date​		Number of Days Rented	2012	2013	2014	2015	2016	2017	2018	2019
7		1/2/2012	1/6/2012		121	121
8		1/5/2018	2/5/2019		366							244	122
9		1/4/2016	1/8/2016		122					122
10		1/8/2016	1/3/2017		212					152	60
11		1/11/2013	1/2/2014		92		60	32
12
13
14

<tbody>
</tbody>

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