Need help with formula tied to dates. Nested IF formula.

[Drummerboy1]

I'm going crazy! I've been trying to get his formula to work for 2 days and I am finally throwing in the towel. Any help would be MUCH appreciated.

On this example Google Sheet:

Copy of Copy - Data Sheet

Datasheet Install Date,Enter Billing Month:,12/1/2021 Date,Payment,Notes,7/1/2021,8/1/2021,9/1/2021,10/1/2021,11/1/2021,12/1/2021,1/1/2022,2/1/2022,3/1/2022,4/1/2022,5/1/2022,Jun-2022,Jul-2022 1,8/4/2021,2,This row contains my errant formulas,,1,1,,,,,,,,,, 2,8/4/2021,6,This row is how it should...

docs.google.com

As the Sheet above explains, I'm trying to do a nested IF formula to accomplish the 4 rules:

Rule 1: If CELL IN column B is blank, leave all corresponding cells (G2:S2) blank

Rule 2: If the date in G$2:S$2 is AFTER the date in Cell $F$1, leave it blank

Rule 3: If the date in B3:B12 is the SAME MONTH, and SAME YEAR, as the dates in G2:S2, put a "2"

Rule 4: Put "1"s in all other cells

Any help you can provide would be SO helpful. I'm not an excel or Google Sheets pro by any means, and I usually find a way to stumble through to the results I need. I can't seem to fix this one though.

Thank you in advance!

Rich

 

[Drummerboy1]

Here is the formula I'm trying to fix:

=IF($B3="","",IF(G$2>$F$1,"",IF(month($B3)&year($B3)>month(G$2)&YEAR(G$2),"",IF(month($B3)&year($B3)=month(L$2)&YEAR(L$2),2,1))))

And a screenshot below.

 

[snjpverma]

Book1
	B	C	D	E	F	G	H	I	J	K	L
1					01 December 2021
2	Install Date					01 July 2021	01 August 2021	01 September 2021	01 October 2021	01 November 2021	01 December 2021
3	04 August 2021					1	2	1	1	1	1
4	04 August 2021					1	2	1	1	1	1
5	01 October 2021					1	1	1	2	1	1
6	02 January 2021					1	1	1	1	1	1
7	04 September 2021					1	1	2	1	1	1
8	31 August 2021					1	2	1	1	1	1
9	01 June 2021					1	1	1	1	1	1
10	31 December 2021					1	1	1	1	1	2
11	05 August 2021					1	2	1	1	1	1
12	05 August 2021					1	2	1	1	1	1
Sheet1

Cell Formulas
Range	Formula
G3:L12	G3	=IF(OR($B3="",G$2>$F$1),"",1+(MONTH($B3)&YEAR($B3)=MONTH(G$2)&YEAR(G$2)))

 

[Drummerboy1]

I figured it out! Only took me 2 days!

 

[Peter_SSs]

Same concept as @snjpverma but a bit shorter way of writing it

21 10 01.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
1					01 December 2021
2	Install Date					01 July 2021	01 August 2021	01 September 2021	01 October 2021	01 November 2021	01 December 2021	01 January 2022
3	04-August-2021					1	2	1	1	1	1
4	04-August-2021					1	2	1	1	1	1
5	01-October-2021					1	1	1	2	1	1
6	02-January-2021					1	1	1	1	1	1
7	04-September-2021					1	1	2	1	1	1
8	31-August-2021					1	2	1	1	1	1
9	01-June-2021					1	1	1	1	1	1
10	31-December-2021					1	1	1	1	1	2
11	05-August-2021					1	2	1	1	1	1
12	05-August-2021					1	2	1	1	1	1
Month & Year

Cell Formulas
Range	Formula
G3:M12	G3	=IF(OR($B3="",G$2>$F$1),"",1+(TEXT($B3,"myy")=TEXT(G$2,"myy")))