Add a 1 in a cell based on a date range

[abrig005]

Hello,
I am trying to figure out the formula for the following: Starting in G2 I want to add a 1 when the dates in C2 and D2 are within the range of G1 and continue on for each cell down and across. The formula I have now does that. However, I cant figure out how to make a formula where:

Add a 1 if the date in C2 (TO) starts on the 1st to the 14th and do not add a 1 if the date in C2 starts on the 15th or later.

Do not add a 1 if the date in D1 (FROM) is 1st through the 14th and add a 1 if the date is on the 15th or later.

thank you!

 

[jtakw]

Hi,

Not absolutely clear on what you're asking, are those 2 separate conditions for 2 separate formulas, or combined, the following is based on your description combined.

Book1
	C	D	E	F	G	H	I
1					18-Jan	18-Feb	18-Mar
2	1/1/2018	3/30/2018			1	1	1
3	1/15/2018	3/30/2018
4	1/1/2018	3/15/2018			1	1
Sheet67

Cell Formulas
Range	Formula
G2	=IF(AND(G$1>=$C2,DAY($C2)<15,G$1<=$D2,DAY($D2)>14),1,"")

 

[abrig005]

Thanks for the help!
I did't explain it thoroughly.

if c begins between 1/1/2018 - 1/14/2018 cell g would get a 1
if d ends between 3/15/2018 - 3/31/2019 cells h and i get a 1 (like you have it)

if cell c begins between 1/15/2018 - 1/31/2018 cell g would not get a 1
if cell d ends between 3/1/2018 - 3/14/2018 only cell H would get a 1 but not I

if cell c = 1/1/2018 and D = 3/15/2018 G, H, I would all have ones

hopefully that explains it better and thank you again!

 

[jtakw]

Ok, took some time and thinking trying to understand your requirement, I believe this will do what you ask.

I can't test for all your possible data, since you just posted a picture and Not something we can copy into Excel, and I don't want to replicate your data manually, so test this out and see if it works for you:

Book1
	C	D	E	F	G	H	I
1					18-Jan	18-Feb	18-Mar
2	1/10/2018	3/25/2018			1	1	1
3	1/15/2018	3/14/2018				1
4	1/1/2018	3/15/2018			1	1	1
Sheet67

Cell Formulas
Range	Formula
G2	=IF(OR(AND(MONTH($C2)=MONTH(G$1),DAY($C2)<15),AND(MONTH(G$1)>MONTH($C2),MONTH(G$1)$D2)),AND(MONTH($D2)=MONTH(G$1),DAY($D2)>14)),1,"")

G2 formula copied down and across as far as needed.

 

[abrig005]

I will test it today. I can't thank you enough for taking the time to figure this out for me!!!!!!!!!!!!!!!!!!!
Have a great day!

 

[jtakw]

Had another look today at your sample, my formula in Post #4 did Not account for the Year, and also in case you have FROM and TO dates that crosses over years (i.e. 2017 to 2019, 2017 to 2020, etc.), Test this Updated formula:

SIDENOTE: your TO and FROM Column Headers seems Backwards?

Book1
	C	D	E	F	G	H	I
1					18-Jan	18-Feb	1-Mar
2	1/10/2018	3/25/2018			1	1	1
3	1/10/2019	3/25/2019
4	1/10/2017	3/25/2019			1	1	1
5	1/15/2018	3/14/2018				1
6	1/1/2018	3/15/2018			1	1	1
Sheet67

Cell Formulas
Range	Formula
G2	=IF(OR(AND(MONTH($C2)=MONTH(G$1),IF(YEAR(G$1)=YEAR($C2),DAY($C2)<15,IF(AND(YEAR(G$1)>YEAR($C2),YEAR(G$1)<=YEAR($D2)),1,0))),AND(MONTH(G$1)>MONTH($C2),MONTH(G$1)$D2),YEAR($C2)<=YEAR(G$1),YEAR($D2)>=YEAR(G$1)),AND(MONTH($D2)=MONTH(G$1),IF(YEAR(G$1)=YEAR($D2),DAY($D2)>14,IF(AND(YEAR(G$1)>=YEAR($C2),YEAR(G$1)$D2)),1,0)))),1,"")

Formula copied down and across as far as needed.