CFDATE

[schardt679]

CFDATE is used for Conditional Formatting and returns TRUE or FALSE given a function number (1-12) related to dates and a cell reference.

Excludes blanks but not 0's.
Functions are 1☛Yesterday, 2☛Today, 3☛Tomorrow, 4☛Last Week, 5☛This Week, 6☛Next Week, 7☛Last Month, 8☛This Month, 9☛Next Month, 10☛Last Year, 11☛This Year, 12☛Next Year
Week_Start uses same values as WEEKDAY's return_type (11-17). Sunday (17) is the default.

=LAMBDA(Func_Num,Cell_Ref,Week_Start,
       LET(Func, Func_Num,    Ref, Cell_Ref,    Week, Week_Start,
           T_Day, TODAY(),  Wk_Day, WEEKDAY(T_Day, IF(Week=0, 17, Week)),
           ThisMon, MONTH(T_Day),    ThisYr, YEAR(T_Day),
           RefMon, MONTH(Ref),    RefYr, YEAR(Ref),
           ThisWkStart, T_Day-Wk_Day+1,    ThisWkEnd, T_Day-Wk_Day+7,
           YES, Ref=T_Day-1,   TOD, Ref=T_Day,    TOM, Ref=T_Day+1,
           WK_L, AND(Ref>=ThisWkStart-7, Ref<=ThisWkEnd-7),
           WK, AND(Ref>=ThisWkStart, Ref<=ThisWkEnd),
           WK_N, AND(Ref>=ThisWkStart+7, Ref<=ThisWkEnd+7),
           MON_L, RefMon=ThisMon-1,
           MON, RefMon=ThisMon,
           MON_N, RefMon=ThisMon+1,
           YR_L, RefYr=ThisYr-1,
           YR, RefYr=ThisYr,
           YR_N, RefYr=ThisYr+1,
           F1_6, SWITCH(Func,  1, YES,  2, TOD,  3, TOM,  4, WK_L,  5, WK,  6, WK_N,  0),
           F7_12, SWITCH(Func,  7, MON_L,  8, MON,  9, MON_N,  10, YR_L,  11, YR,  12, YR_N,  0),
           Result, IF(--(F1_6)=1, F1_6, F7_12),
           Return, IF(Ref="", 0, Result),
           Return
       )
  )

LAMBDA Examples.xlsx
	A	B	C	D	E	F	G	H
1		CFDATE
2
3		Sample Data				Conditional Formatting Colors
4		Yeserday	Today	Tomorrow		Func_Num	Function
5		06/28/2021	06/28/2021	06/28/2021		1	Yesterday (YES)
6		06/29/2021	06/29/2021	06/29/2021		2	Today (TOD)
7		06/30/2021	06/30/2021	06/30/2021		3	Tomorrow (TOM)
8		07/01/2021	07/01/2021	07/01/2021
9		07/02/2021	07/02/2021	07/02/2021
10		07/03/2021	07/03/2021	07/03/2021
11		07/04/2021	07/04/2021	07/04/2021
12
13
14		Sample Data				Conditional Formatting Colors
15		Last Week	This Week	Next Week		Func_Num	Function
16		06/16/2021	06/16/2021	06/16/2021		4	Last Week (WK_L)
17		06/23/2021	06/23/2021	06/23/2021		5	This Week (WK)
18		07/01/2021	07/01/2021	07/01/2021		6	Next Week (WK_N)
19		07/02/2021	07/02/2021	07/02/2021
20		07/07/2021	07/07/2021	07/07/2021
21		07/14/2021	07/14/2021	07/14/2021
22		07/21/2021	07/21/2021	07/21/2021
23
24
25		Sample Data				Conditional Formatting Colors
26		Last Month	This Month	Next Month		Func_Num	Function
27		05/03/2021	05/03/2021	05/03/2021		7	Last Month (MON_L)
28		05/08/2021	05/08/2021	05/08/2021		8	This Month (MON)
29		06/20/2021	06/20/2021	06/20/2021		9	Next Month (MON_N)
30		06/11/2021	06/11/2021	06/11/2021
31		06/30/2021	06/30/2021	06/30/2021
32		07/04/2021	07/04/2021	07/04/2021
33		07/25/2021	07/25/2021	07/25/2021
34
35
36		Sample Data				Conditional Formatting Colors
37		Last Year	This Year	Next Year		Func_Num	Function
38		02/21/2020	02/21/2020	02/21/2020		10	Last Year(YR_L)
39		07/03/2020	07/03/2020	07/03/2020		11	This Year (YR)
40		01/06/2021	01/06/2021	01/06/2021		12	Next Year (YR_N)
41		08/03/2021	08/03/2021	08/03/2021
42		03/23/2022	03/23/2022	03/23/2022
43		05/30/2022	05/30/2022	05/30/2022
44		07/21/2022	07/21/2022	07/21/2022
45
CFDATE

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:B11	Expression	=CFDATE(1, B5,)	text	NO
C5:C11	Expression	=CFDATE(2, C5,)	text	NO
D5:D11	Expression	=CFDATE(3, D5,)	text	NO
B16:B22	Expression	=CFDATE(4, B16, 17)	text	NO
C16:C22	Expression	=CFDATE(5, C16, 17)	text	NO
D16:D22	Expression	=CFDATE(6, D16, 17)	text	NO
B27:B33	Expression	=CFDATE(7, B27,)	text	NO
C27:C33	Expression	=CFDATE(8, C27,)	text	NO
D27:D33	Expression	=CFDATE(9, D27,)	text	NO
B38:B44	Expression	=CFDATE(10, B38,)	text	NO
C38:C44	Expression	=CFDATE(11, C38,)	text	NO
D38:D44	Expression	=CFDATE(12, D38,)	text	NO