Conditional Formatting based on passed date

[SnKeaK]

		SEP	D	E	F	G
45536	09/01/2024	01	9	$15.93	$.90	$1.77
45537	09/02/2024	02	11	$100	$.32	$2.45
45538	09/03/2024	03	19	$232	$3	$4.56
45539	09/04/2024	04

In the example above, I am trying to conditionally format cells in the column labeled D through G. The objective is I need two criteria met.

1. Cells in D column, D2 for example must be greater than 0
2. Cells in B column, B2 for example date must be less than today. So in other words, I only want the cells in that row highlighted if it's past the current date.

=AND($D2>0,DATEVALUE(B2)<TODAY() -----is being applied to =$D$2:$G$5 with no success.

I need D2 to G2 highlighted after Sep 1st passes. Then once Sep 2nd arrives/passes, highlight D3 to G3. And so on.

 

[etaf]

the dates appear left justified , as does the numbers
are they real dates & numbers ?

normally dates and numbers are right justified
on the date column
change the format to general all the dates should change to numbers

select D2:G10000 or however many rows yoy need

=AND( $B2<>"", $B2<today() , $D2>0)

otherwise

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[etaf]

you need to put the info as a reply here
B is not a date as you are using
=TEXT(A2,"mm/dd/yyyy")
so you cannot use <today()
so not sure why you are doing that, as A is a date
=AND( $A2<>"", $A2<today() , $D2>0)

Coco Lotto Master Tracker.xlsx
	A	B	C	D	E	F	G
1			SEP	Total # of Entries	Prize Pool	Monthly Wheel Dues	Commision Earned
2	45536	09/01/2024	01	9	$15.93	$0.90	$1.77
3	45537	09/02/2024	02	11		$1.10	#VALUE!
4	45538	09/03/2024	03	11		$1.10	#VALUE!
5	45539	09/04/2024	04	11		$1.10	#VALUE!
6	45540	09/05/2024	05	11		$1.10	#VALUE!
7	45541	09/06/2024	06	11		$1.10	#VALUE!
8	45542	09/07/2024	07	11		$1.10	#VALUE!
9	45543	09/08/2024	08	11		$1.10	#VALUE!
10	45544	09/09/2024	09
11	45545	09/10/2024	10
12	45546	09/11/2024	11
13	45547	09/12/2024	12
14	45548	09/13/2024	13	#VALUE!		#VALUE!	#VALUE!
15	45549	09/14/2024	14	#VALUE!		#VALUE!	#VALUE!
16	45550	09/15/2024	15	#VALUE!		#VALUE!	#VALUE!
17	45551	09/16/2024	16	#VALUE!		#VALUE!	#VALUE!
18	45552	09/17/2024	17	#VALUE!		#VALUE!	#VALUE!
19	45553	09/18/2024	18	#VALUE!		#VALUE!	#VALUE!
20	45554	09/19/2024	19	#VALUE!		#VALUE!	#VALUE!
21	45555	09/20/2024	20	#VALUE!		#VALUE!	#VALUE!
22	45556	09/21/2024	21	#VALUE!		#VALUE!	#VALUE!
23	45557	09/22/2024	22	#VALUE!		#VALUE!	#VALUE!
24	45558	09/23/2024	23	#VALUE!		#VALUE!	#VALUE!
25	45559	09/24/2024	24	#VALUE!		#VALUE!	#VALUE!
26	45560	09/25/2024	25	#VALUE!		#VALUE!	#VALUE!
27	45561	09/26/2024	26	#VALUE!		#VALUE!	#VALUE!
28	45562	09/27/2024	27	#VALUE!		#VALUE!	#VALUE!
29	45563	09/28/2024	28	#VALUE!		#VALUE!	#VALUE!
30	45564	09/29/2024	29	#VALUE!		#VALUE!	#VALUE!
31	45565	09/30/2024	30	#VALUE!			#VALUE!
Monthly Entry Report 9-24

Cell Formulas
Range	Formula
B2:B31	B2	=TEXT(A2,"mm/dd/yyyy")
C2:C31	C2	=LEFT(MID(B2,FIND("/",B2)+1,LEN(B2)),FIND("/",MID(B2,FIND("/",B2)+1,LEN(B2)))-1)
D3:D31	D3	=IF(TODAY()>=DATEVALUE(B3),IF(COUNTA($L$2:$L$31)<=0,"",COUNTA($L$2:$L$31)),"")
E3:E31	E3	=IFERROR(SUM(O$2-[@[Monthly Wheel Dues]]-[@[Commision Earned]]),"")
F3:F30	F3	=IF([@[Total '# of Entries]]="","",[@[Total '# of Entries]]*0.1)
G3:G31	G3	=IF(TODAY()>=DATEVALUE(B3),IF(Table14[@[Tips Received ]]="","",SUM(O$2-O$4)*10%),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:G31	Expression	=AND( $A2<>"", $A2<TODAY(), $D2>0)	text	YES

 

[SnKeaK]

the dates appear left justified , as does the numbers
are they real dates & numbers ?

normally dates and numbers are right justified
on the date column
change the format to general all the dates should change to numbers

select D2:G10000 or however many rows yoy need

=AND( $B2<>"", $B2<today() , $D2>0)

otherwise

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

you need to put the info as a reply here
B is not a date as you are using
=TEXT(A2,"mm/dd/yyyy")
so you cannot use <today()
so not sure why you are doing that, as A is a date
=AND( $A2<>"", $A2<today() , $D2>0)

Coco Lotto Master Tracker.xlsx
	A	B	C	D	E	F	G
1			SEP	Total # of Entries	Prize Pool	Monthly Wheel Dues	Commision Earned
2	45536	09/01/2024	01	9	$15.93	$0.90	$1.77
3	45537	09/02/2024	02	11		$1.10	#VALUE!
4	45538	09/03/2024	03	11		$1.10	#VALUE!
5	45539	09/04/2024	04	11		$1.10	#VALUE!
6	45540	09/05/2024	05	11		$1.10	#VALUE!
7	45541	09/06/2024	06	11		$1.10	#VALUE!
8	45542	09/07/2024	07	11		$1.10	#VALUE!
9	45543	09/08/2024	08	11		$1.10	#VALUE!
10	45544	09/09/2024	09
11	45545	09/10/2024	10
12	45546	09/11/2024	11
13	45547	09/12/2024	12
14	45548	09/13/2024	13	#VALUE!		#VALUE!	#VALUE!
15	45549	09/14/2024	14	#VALUE!		#VALUE!	#VALUE!
16	45550	09/15/2024	15	#VALUE!		#VALUE!	#VALUE!
17	45551	09/16/2024	16	#VALUE!		#VALUE!	#VALUE!
18	45552	09/17/2024	17	#VALUE!		#VALUE!	#VALUE!
19	45553	09/18/2024	18	#VALUE!		#VALUE!	#VALUE!
20	45554	09/19/2024	19	#VALUE!		#VALUE!	#VALUE!
21	45555	09/20/2024	20	#VALUE!		#VALUE!	#VALUE!
22	45556	09/21/2024	21	#VALUE!		#VALUE!	#VALUE!
23	45557	09/22/2024	22	#VALUE!		#VALUE!	#VALUE!
24	45558	09/23/2024	23	#VALUE!		#VALUE!	#VALUE!
25	45559	09/24/2024	24	#VALUE!		#VALUE!	#VALUE!
26	45560	09/25/2024	25	#VALUE!		#VALUE!	#VALUE!
27	45561	09/26/2024	26	#VALUE!		#VALUE!	#VALUE!
28	45562	09/27/2024	27	#VALUE!		#VALUE!	#VALUE!
29	45563	09/28/2024	28	#VALUE!		#VALUE!	#VALUE!
30	45564	09/29/2024	29	#VALUE!		#VALUE!	#VALUE!
31	45565	09/30/2024	30	#VALUE!			#VALUE!
Monthly Entry Report 9-24

Cell Formulas
Range	Formula
B2:B31	B2	=TEXT(A2,"mm/dd/yyyy")
C2:C31	C2	=LEFT(MID(B2,FIND("/",B2)+1,LEN(B2)),FIND("/",MID(B2,FIND("/",B2)+1,LEN(B2)))-1)
D3:D31	D3	=IF(TODAY()>=DATEVALUE(B3),IF(COUNTA($L$2:$L$31)<=0,"",COUNTA($L$2:$L$31)),"")
E3:E31	E3	=IFERROR(SUM(O$2-[@[Monthly Wheel Dues]]-[@[Commision Earned]]),"")
F3:F30	F3	=IF([@[Total '# of Entries]]="","",[@[Total '# of Entries]]*0.1)
G3:G31	G3	=IF(TODAY()>=DATEVALUE(B3),IF(Table14[@[Tips Received ]]="","",SUM(O$2-O$4)*10%),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:G31	Expression	=AND( $A2<>"", $A2<TODAY(), $D2>0)	text	YES

you need to put the info as a reply here
B is not a date as you are using
=TEXT(A2,"mm/dd/yyyy")
so you cannot use <today()
so not sure why you are doing that, as A is a date
=AND( $A2<>"", $A2<today() , $D2>0)

Coco Lotto Master Tracker.xlsx
	A	B	C	D	E	F	G
1			SEP	Total # of Entries	Prize Pool	Monthly Wheel Dues	Commision Earned
2	45536	09/01/2024	01	9	$15.93	$0.90	$1.77
3	45537	09/02/2024	02	11		$1.10	#VALUE!
4	45538	09/03/2024	03	11		$1.10	#VALUE!
5	45539	09/04/2024	04	11		$1.10	#VALUE!
6	45540	09/05/2024	05	11		$1.10	#VALUE!
7	45541	09/06/2024	06	11		$1.10	#VALUE!
8	45542	09/07/2024	07	11		$1.10	#VALUE!
9	45543	09/08/2024	08	11		$1.10	#VALUE!
10	45544	09/09/2024	09
11	45545	09/10/2024	10
12	45546	09/11/2024	11
13	45547	09/12/2024	12
14	45548	09/13/2024	13	#VALUE!		#VALUE!	#VALUE!
15	45549	09/14/2024	14	#VALUE!		#VALUE!	#VALUE!
16	45550	09/15/2024	15	#VALUE!		#VALUE!	#VALUE!
17	45551	09/16/2024	16	#VALUE!		#VALUE!	#VALUE!
18	45552	09/17/2024	17	#VALUE!		#VALUE!	#VALUE!
19	45553	09/18/2024	18	#VALUE!		#VALUE!	#VALUE!
20	45554	09/19/2024	19	#VALUE!		#VALUE!	#VALUE!
21	45555	09/20/2024	20	#VALUE!		#VALUE!	#VALUE!
22	45556	09/21/2024	21	#VALUE!		#VALUE!	#VALUE!
23	45557	09/22/2024	22	#VALUE!		#VALUE!	#VALUE!
24	45558	09/23/2024	23	#VALUE!		#VALUE!	#VALUE!
25	45559	09/24/2024	24	#VALUE!		#VALUE!	#VALUE!
26	45560	09/25/2024	25	#VALUE!		#VALUE!	#VALUE!
27	45561	09/26/2024	26	#VALUE!		#VALUE!	#VALUE!
28	45562	09/27/2024	27	#VALUE!		#VALUE!	#VALUE!
29	45563	09/28/2024	28	#VALUE!		#VALUE!	#VALUE!
30	45564	09/29/2024	29	#VALUE!		#VALUE!	#VALUE!
31	45565	09/30/2024	30	#VALUE!			#VALUE!
Monthly Entry Report 9-24

Cell Formulas
Range	Formula
B2:B31	B2	=TEXT(A2,"mm/dd/yyyy")
C2:C31	C2	=LEFT(MID(B2,FIND("/",B2)+1,LEN(B2)),FIND("/",MID(B2,FIND("/",B2)+1,LEN(B2)))-1)
D3:D31	D3	=IF(TODAY()>=DATEVALUE(B3),IF(COUNTA($L$2:$L$31)<=0,"",COUNTA($L$2:$L$31)),"")
E3:E31	E3	=IFERROR(SUM(O$2-[@[Monthly Wheel Dues]]-[@[Commision Earned]]),"")
F3:F30	F3	=IF([@[Total '# of Entries]]="","",[@[Total '# of Entries]]*0.1)
G3:G31	G3	=IF(TODAY()>=DATEVALUE(B3),IF(Table14[@[Tips Received ]]="","",SUM(O$2-O$4)*10%),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:G31	Expression	=AND( $A2<>"", $A2<TODAY(), $D2>0)	text	YES

I really appreciate your help. Thank you.

 

[etaf]

how thats solved it for you - WHY are you turning A into B with text()
just format A to the display you want ?

 

[SnKeaK]

how thats solved it for you - WHY are you turning A into B with text()
just format A to the display you want ?

Well, I'm familiar with a few things in excel, but as you know, newbs make things a lot more difficult than it has to be. That's what I did here. Check column C's formula and you'll see why I did that. Just playing with stuff I guess. And I thought it was solved after using column A like you suggested.

 

[etaf]

ok, everyone starts somewhere
anyway
if you just want to show the day of the month
=DAY()

and use a custom format
0#

Coco Lotto Master Tracker.xlsx
	A	B	C	D
1			SEP	Total # of Entries
2	45536	09/01/2024	01	9
3	45537	09/02/2024	02	11
4	45538	09/03/2024	03	11
5	45539	09/04/2024	04	11
6	45540	09/05/2024	05	11
7	45541	09/06/2024	06	11
8	45542	09/07/2024	07	11
9	45543	09/08/2024	08	11
10	45544	09/09/2024	09
11	45545	09/10/2024	10
12	45546	09/11/2024	11
13	45547	09/12/2024	12
14	45548	09/13/2024	13	#VALUE!
15	45549	09/14/2024	14	#VALUE!
Monthly Entry Report 9-24

Cell Formulas
Range	Formula
B2:B15	B2	=TEXT(A2,"mm/dd/yyyy")
C2:C15	C2	=DAY(A2)
D3:D15	D3	=IF(TODAY()>=DATEVALUE(B3),IF(COUNTA($L$2:$L$31)<=0,"",COUNTA($L$2:$L$31)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:G31	Expression	=AND($B2<>"",$B2<TODAY(),$D2>0)	text	NO

 

[SnKeaK]

ok, everyone starts somewhere
anyway
if you just want to show the day of the month
=DAY()

and use a custom format
0#

Coco Lotto Master Tracker.xlsx
	A	B	C	D
1			SEP	Total # of Entries
2	45536	09/01/2024	01	9
3	45537	09/02/2024	02	11
4	45538	09/03/2024	03	11
5	45539	09/04/2024	04	11
6	45540	09/05/2024	05	11
7	45541	09/06/2024	06	11
8	45542	09/07/2024	07	11
9	45543	09/08/2024	08	11
10	45544	09/09/2024	09
11	45545	09/10/2024	10
12	45546	09/11/2024	11
13	45547	09/12/2024	12
14	45548	09/13/2024	13	#VALUE!
15	45549	09/14/2024	14	#VALUE!
Monthly Entry Report 9-24

Cell Formulas
Range	Formula
B2:B15	B2	=TEXT(A2,"mm/dd/yyyy")
C2:C15	C2	=DAY(A2)
D3:D15	D3	=IF(TODAY()>=DATEVALUE(B3),IF(COUNTA($L$2:$L$31)<=0,"",COUNTA($L$2:$L$31)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:G31	Expression	=AND($B2<>"",$B2<TODAY(),$D2>0)	text	NO

Cell	Condition	Cell Format	Stop If True
D2:G31	Expression	=AND($B2<>"",$B2<TODAY(),$D2>0)	text	NO

After changing the CF to this, nothing highlights. I'm just confused. I'll post link to show what I have. I'm not sure how to use the built in feature here. Permissions got denied when I tried. Coco Lotto Master Tracker.xlsx

 

[etaf]

why change to
=AND($B2<>"",$B2<TODAY(),$D2>0)
that was the original suggestion , BUT column B is text
=TEXT(A2,"mm/dd/yyyy")
based on the real date in A2
so as mentioned try using A instead of B
=AND($A2<>"",$A2<TODAY(),$D2>0)

 

[SnKeaK]

why change to
=AND($B2<>"",$B2<TODAY(),$D2>0)
that was the original suggestion , BUT column B is text
=TEXT(A2,"mm/dd/yyyy")
based on the real date in A2
so as mentioned try using A instead of B
=AND($A2<>"",$A2<TODAY(),$D2>0)

Etaf, so your formula works great. However I have a problem with a particular row where the conditional formatting simply refuses to work. Can you take another look at my worksheet and see if you can spot the problem? It is Row 3, D3:G3

https://1drv.ms/x/c/f7b9915682722e86/EQa4FmNLK7xEnVexYmVntocBYp6VwJ4k4Qq9uN7YmhIJNQ?e=XrvAlR&nav=MTVfezMzQjYxMUJGLTJENzAtNDkyQi04MEVCLTlGRkM0QTlGMzIxOH0