Contract with start and end date in a specific quarter

[psvhans]

Hi All,

I have a list of contracts with all different start and end dates. Could be any date. I would like to see per contract for which quarter(s) they are valid.
Also if a contract is just 2 days open in a quarter, I need to mark them as YES open in that quarter.

See below table. Any nice formula to get these yes/no's?


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Contract[/TD]
[TD]Start date[/TD]
[TD]End date[/TD]
[TD]Q1-16[/TD]
[TD]Q2-16[/TD]
[TD]Q3-16[/TD]
[TD]Q4-16[/TD]
[TD]Q1-17[/TD]
[TD]Q2-17[/TD]
[TD]Q3-17[/TD]
[TD]Q4-17[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]01-01-2016[/TD]
[TD]01-05-2016[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]10-04-2016[/TD]
[TD]15-10-2016[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]01-08-2016[/TD]
[TD]10-01-2017[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

 

[CROY1985]

I would start by adding in 2 rows with the quarter start date and end date. You can hide them afterwards.

You can then use the following:

=IF(AND(D$2>$B4,D$3<$C4),"Yes",IF(AND($B4>=D$2,$B4<=D$3),"Yes",IF(AND($C4>=D$2,$C4<=D$3),"Yes","No")))

Where Row 1 has your original headings,
Where Row 2 has quarter start dates,
Where Row 3 has quarter end dates,
Where Column A has Contract Names
Where Column B has Contract Start Dates,
Where Column C has Contract End Dates,