Returning 1 or 0 based on multiple dates

[surkdidat]

Hi

At the moment I have the following formula - =

IF(AND($G$2<>"",R$1>$G2,R$1<$H2),1,0)

whereby there is a start date is column G, and an end date in column H, and its look at the column headers for dates to see if a date is between them. The formaula works fine, however, I have multiple datart/end dates in a row, and rather than keep writing the formula out, is there an easier win (there can be anywhere between 1 lot of start/end dates, to 15 start/end dates.

Hopefully below is correct, but essentially its any Start/End date combination that the dates fall between, - so in the date ranges if the last day of the month falls between them, I need a 1, and if not a 0. There will be no overlapping dates between different start/end date combinations.

All rows will have at least one start/end date.

Column G - StartDate 1	Column H - End Date 1	Column I - StartDate 2	Column J - End Date 2	Column K - Start Date 3	Column L - End Date 3	Column AA - 31/03/23	Column AB - 30/04/23	Column AC - 31/05/23	Column AD - 30/06/23	Column AE - 31/07/23
15/03/23	18/05/23					1	1	0	0	0
15/03/23	02/04/23	05/04/23	02/05/23			1	1	0	0	0
15/04/23	01/05/23	01/06/23	01/07/23			0	1	0	1	0

 

[SanjayGMusafir]

• There is no column R shown in your sample data to understand the exact situation.
• Post a larger sample data using XL2BB.
• Do you need these 0s, 1s for any further calculation or what?

 

[bebo021999]

Suppose first start_date starts from odd column (column G) (in formula there is ISODD then ISEVEN. If actual range start from even column, i.e column H, use ISEVEN then ISODD next)

Book1
	G	H	I	J	K	L														Z	AA	AB	AC	AD	AE	AF	AG
1	Start Date 1	End Date 1	Start Date 2	End Date 2	Start Date 3	End Date 3															31-03-23	30-04-23	31-05-23	30-06-23	31-07-23	31-08-23	30-09-23
2	15-03-23	18-05-23																			1	1	0	0	0	0	0
3	15-03-23	02-04-23	05-04-23	02-05-23																	1	1	0	0	0	0	0
4	15-04-23	01-05-23	01-06-23	01-07-23																	0	1	0	1	0	0	0
Sheet1

Cell Formulas
Range	Formula
AB1:AG1	AB1	=EOMONTH(AA1,1)
AA2:AG4	AA2	=SUMPRODUCT(((AA$1>=$G2:$K2)*ISODD(COLUMN($G2:$K2)))*(AA$1<=$H2:$L2)*ISEVEN(COLUMN($H2:$L2)))