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?

Contract	Start date	End date	Q1-16	Q2-16	Q3-16	Q4-16	Q1-17	Q2-17	Q3-17	Q4-17
A	01-01-2016	01-05-2016	Yes	Yes	No	No	No	No	No	No
B	10-04-2016	15-10-2016	No	Yes	Yes	Yes	No	No	No	No
C	01-08-2016	10-01-2017	No	No	Yes	Yes	Yes	No	No	No

<tbody>
</tbody>

 

[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,