conditional formatting the most recent day of the month in a list

[jbrown021286]

bills 2024.xlsb.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	bill	rough date	amount	balance needed				base balance					additional funds					date
2	payday	15	$2,467.48	$ 3,967.48														5/13/2024
3	credit card	15	$ 800.00	$ 3,167.48				$ 1,500.00
4	hulu	17	$ 81.99	$ 3,085.49									.
5	pilot	19	$ 152.88	$ 2,932.61	Payments left
6					33
7	dee med bill	19	$ 385.31	$ 2,547.30	Payments left
8					7
9	netflix	22	$ 15.49	$ 2,531.81
10	Rachel med bill	24	$ 103.65	$ 2,428.16	Payments left
11					26			total bills
12	usaa card	25	$ 205.08	$ 2,223.08
13	internet	28	$ 55.00	$ 2,168.08				$ 4,934.95						12 month avg
14	phone	27	$ 203.00	$ 1,965.08
15	tools	29	$ 100.00	$ 1,865.08
16	payday	30	$2,467.48	$ 4,332.55				months end balance
17	credit card	30	$ 800.00	$ 3,532.55
18	water	2	$ 54.77	$ 3,477.78				$ -
19	house	4	$1,222.31	$ 2,255.47
20	power	6	$ 204.47	$ 2,051.00
21	trash	6	$ 29.00	$ 2,022.00
22	grass	8	$ 110.00	$ 1,912.00
23	tools	8	$ 100.00	$ 1,812.00
24	church	10	$ 312.00	$ 1,500.00
bills

Cell Formulas
Range	Formula
C2	C2	=H13/2
R2	R2	=TODAY()
E6	E6	=debt!B3
E8	E8	=debt!F3
E11	E11	=debt!J3
H13	H13	=SUM(C17:C24,C3:C15)
C16	C16	=H13/2
H18	H18	=D24-M3-H3
D2	D2	=H3+C2+M3+M17
D3:D5,D10,D13:D15,D17:D24	D3	=D2-C3
D7,D9,D12	D7	=D5-C7
D16	D16	=D15+C16

im not sure it is possible but here is what i am wanting to do. in column B i have the day of the month of when a bill posts and in r2 i have the current date automatically filling. i am wanting to conditionally format the cells in a, b, c and d so that it highlights the most recent date that a bill posted. in this example since the date in R2 is the 13th of the month the last date a bill was posted was on the 10th so i would need A24, B24, C24 and D24 to be highlighted. any help would be appreciated.

 

[awoohaw]

Will this help? Note: I've changed the date to a fixed date, you can re-insert the Today() function as you see fit.

Book1
	A	B	C	D	E													R	S
1	bill	rough date	amount	balance needed														date	building CF formula
2	payday	15	2467.475	3967.475														2024-05-19	FALSE
3	credit card	15	800	3167.475															FALSE
4	hulu	17	81.99	3085.485															FALSE
5	pilot	19	152.88	2932.605															TRUE
6	dee med bill	19	385.31	2547.295															TRUE
7	netflix	22	15.49	2531.805															FALSE
8	Rachel med bill	24	103.65	2428.155															FALSE
9	usaa card	25	205.08	2223.075															FALSE
10	internet	28	55	2168.075															FALSE
11	phone	27	203	1965.075															FALSE
12	tools	29	100	1865.075															FALSE
13	payday	30	2467.475	4332.55															FALSE
14	credit card	30	800	3532.55															FALSE
15	water	2	54.77	3477.78															FALSE
16	house	4	1222.31	2255.47															FALSE
17	power	6	204.47	2051															FALSE
18	trash	6	29	2022															FALSE
19	grass	8	110	1912															FALSE
20	tools	8	100	1812															FALSE
21	church	10	312	1500															FALSE
Sheet1

Cell Formulas
Range	Formula
C2	C2	=H10/2
C13	C13	=H10/2
D2	D2	=H3+C2+M3+M14
D3:D12,D14:D21	D3	=D2-C3
D13	D13	=D12+C13
S2:S21	S2	=MAX(IF(DAY($R$2)>=$B$2:$B$21,$B$2:$B$21,0))=$B2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D21	Expression	=MAX(IF(DAY($R$2)>=$B$2:$B$21,$B$2:$B$21,0))=$B2	text	NO