List of dates and non-continuous date ranges

[dreamerman]

Hi, I am checking a list of overdue receivables against start/end contract dates. Would be easier if contract periods are continuous but sales people don't think like accountants do. Anyways, in the example (pic), column A is the list of overdue invoices of a client. Column C and D are start and end dates of the client's contract. How do I formularise so that I can find out if overdue invoices are covered under contracts or outside contract periods? There are 200+ overdue invoices over 50+ clients so I can't possible do it manually. Any help will be appreciated.

 

[Taul]

Hi,
In cell E2 use this formula

=IF(AND(A2>=C2,A2<=D2),"within contract","outside")

hope this helps
Paul.

 

[dreamerman]

Hi,
In cell E2 use this formula

=IF(AND(A2>=C2,A2<=D2),"within contract","outside")

hope this helps
Paul.

Thanks Paul. Unfortunately it is not row by row but to see it dates in A1:A6 is within date ranges from C1:D6.

 

[sandy666]

is that what you want ?

InvoiceDate	ContractStart	ContractEnd		ContractStart	ContractEnd	Subtraction	ListDates
01/03/2020	01/02/2020	30/11/2020		01/02/2020	30/11/2020	303	01/03/2020
15/01/2020	01/11/2018	31/12/2019		01/03/2018	15/10/2018	228	05/06/2018
31/10/2018	01/03/2018	15/10/2018		01/08/2016	15/05/2017	287	01/04/2017
05/06/2018	18/05/2017	01/02/2018
01/04/2017	01/08/2016	15/05/2017

ListDates contain Dates in the ranges Start/End

 

[dreamerman]

is that what you want ?

Hi Paul, I think below image explains my situation better. What will make my life easy is against each date in column A whether said date is within range of dates in column C:D. Cheers

 

[dreamerman]

Hi Paul, I think below image explains my situation better. What will make my life easy is against each date in column A whether said date is within range of dates in column C:D. Cheers

Sorry I mean Sandy

 

[sandy666]

you can edit your post and change (10 minutes)

 

[sandy666]

read how to use XL2BB to post your data AND expected result
update your profile (Account details) about Excel version and OS

 

[Peter_SSs]

you can edit your post and change (10 minutes)

FYI: Members with a low post count cannot.

@dreamerman
Try this

20 11 11.xlsm
	A	B	C	D
2	1/03/2020	In	1/02/2020	30/11/2020
3	15/01/2020	Out	1/11/2018	31/12/2019
4	1/05/2019	In	1/03/2018	15/10/2018
5	31/10/2018	Out	18/05/2017	1/02/2018
6	1/08/2018	In	1/08/2016	15/05/2017
7	5/06/2018	In
8	16/05/2017	Out
9	1/04/2017	In
In Out

Cell Formulas
Range	Formula
B2:B9	B2	=IF(COUNTIFS(C$2:C$6,"<="&A2,D$2:D$6,">="&A2),"In","Out")