formula to count number of days between two dates that fall into two other dates?

[tonywatsonhelp]

Hi Everyone,

OK so this is the situation.

I have A start Date and an end date for my report.

StartDate = T1
EndDate = U1

Then I have contract start and end dates down Columns P & Q
Contract Start Date Column P
Contract End Date Column Q

So I need a formula to tell me how Many days are between the Dates P & Q but only the ones within T1 & U1.

My Head is spinning but hopefully this makes sense to someone lol.

Hear a small chart example


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]Results[/TD]
[TD][/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]Explanations [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Contract Start Date[/TD]
[TD]Contract End Date[/TD]
[TD]Number of Days[/TD]
[TD][/TD]
[TD]15/05/2018[/TD]
[TD]30/05/2018[/TD]
[TD][TABLE="class: grid, width: 500"]
<tbody style="border-collapse: collapse; width: auto;">[TR]
[TD]
[/TD]
[TD]So Number of days is what i'm looking for a formula for[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01/01/2017[/TD]
[TD]01/01/2019[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD]with this one the start and end date are before and after T & U dates so we count just the days within T & U so its = 15[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20/05/2018[/TD]
[TD]01/01/2020[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD]in this case the start date is after the T1 date so we only count from start date to U1 date[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01/01/2017[/TD]
[TD]20/05/2018[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]with this one only up to the U1 end date as its before U1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01/01/2017[/TD]
[TD]01/02/2017[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]with this one the dates don't fall between so no amount same if it was after[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]hopefully you are getting the idea
I just want to count the number of days within the Start to End dates that fall into my report.

[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Please Help If YOu Can[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/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][/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][/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]
[/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]
[/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]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

Book1
	P	Q	R	S	T	U
1	Contract Start Date	Contract End Date	Number of Days		15/05/2018	30/05/2018
2	01/01/2017	01/01/2019	15
3	20/05/2018	01/01/2020	10
4	01/01/2017	20/05/2018	5
5	01/01/2017	01/02/2017	0
Sheet1

Cell Formulas
Range	Formula
R2	=MAX(MIN($Q2,$U$1)-MAX($P2,$T$1),0)

WBD

 

[tonywatsonhelp]

Thanks Wideboydixon,
Great idea does exactly what I want.
Tony