Help with Date and Conditional formatting

[maximillianrg]

Hello Excel Masters

in cell G25 I have the date 22-JAN-2024 which was populated using the below formula

=IF(COUNTIF(Helper!A:A,LEFT(TEXTAFTER(L25,"ETA: "),8))=1,LEFT(TEXTAFTER(L25,"ETA: "),8),IF(COUNTIF(Helper!B:B,LEFT(TEXTAFTER(L25,"ETA: "),11))=1,LEFT(TEXTAFTER(L25,"ETA: "),11),"Date Format Error"))

The below formula returns False if the date in cell G25 is in the future and False if it is in the past

=IF(DATEVALUE(TEXT(TODAY(),"dd-MMM-yyyy"))>DATEVALUE(G25),"T","F")

I want to set up conditional formatting to highlight all cells in column G that are in the past so I need help tweeking the above formula.

Note: Due to the way I am extracting the date in the first formula using > today() does not work

 

[6StringJazzer]

in cell G25 I have the date 22-JAN-2024

To be very clear, in G25 you have the text string "22-JAN-2024". It is not a date, it just looks like a date.

Your conditional formatting formula needs be an expression that evaluates to TRUE or FALSE, not "T" or "F". Also your date processing is much more complicated than it needs to be. Simplify to:

=TODAY()>DATEVALUE(G25)

$scratch.xlsm
	G
1	01-JAN-2024
2	02-JAN-2024
3	03-JAN-2024
4	04-JAN-2024
5	05-JAN-2024
6	06-JAN-2024
7	07-JAN-2024
8	08-JAN-2024
9	09-JAN-2024
10	10-JAN-2024
11	11-JAN-2024
12	12-JAN-2024
Number Square

Cell Formulas
Range	Formula
G1:G12	G1	=UPPER(TEXT(F1,"dd-MMM-yyyy"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G1:G12	Expression	=TODAY()>DATEVALUE(G1)	text	NO

 

[6StringJazzer]

Afterthought: I don't know what else you are doing with the data in column G but you might be better off enclosing the whole formula in DATEVAULE() so that it is date data instead of text.