Excel条件格式公式：对比带前缀日期与对应列日期并高亮晚日期单元格

Got it, let's break this down to solve your problem—whether you're on an older Excel version or the latest 365, we've got you covered.

Core Challenge #

You need to extract the actual date from cells with leading initials (like J 2024/5/1 or J M 2024/5/10) and compare it to the standard date in column 1, then highlight cells where the extracted date is later. Plus, this rule needs to scale to hundreds of columns.

Option 1: Compatible with All Excel Versions #

Use this formula for conditional formatting—it works even in older Excel versions (pre-365):

=LOOKUP(9^9,--MID(B1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B1&"0123456789")),ROW($1:$100)))>$A1

How This Formula Works: #

• B1&"0123456789": Ensures we can always find a digit (failsafe for edge cases where a cell might be empty).
• MIN(FIND({0,1,2,3,4,5,6,7,8,9},...)): Locates the position of the first digit in the cell—this is where the date starts, skipping all leading initials.
• MID(B1, [first digit position], ROW($1:$100)): Extracts text starting from the first digit, up to 100 characters (more than enough for any date format).
• --: Converts the extracted text to a numeric value (Excel stores dates as numbers, so valid date text will convert correctly).
• LOOKUP(9^9, ...): Ignores any errors from non-date text (like the initials) and grabs the last valid numeric value—this is your full date.
• >$A1: Compares the extracted date to the standard date in column A (adjust $A1 if your standard date column is different).

Option 2: Simplified for Excel 365/2021+ #

If you're using a modern Excel version with dynamic array functions, this shorter formula does the same job:

=TEXTAFTER(B1," ",-1)*1>$A1

How This Works: #

• TEXTAFTER(B1," ",-1): Extracts the text after the last space in the cell—perfect for skipping any number of leading initials.
• *1: Converts the extracted date text to a numeric date value.
• >$A1: Compares to the standard date in column A.

How to Apply the Rule to Hundreds of Columns #

1. Select all target columns: Click the first column letter (e.g., B), hold Shift, then click the last column letter you need (e.g., ZZ) to select hundreds of columns at once.
2. Create the conditional format rule:
   • Go to the Home tab → Conditional Formatting → New Rule.
   • Choose Use a formula to determine which cells to format.
   • Paste the formula from above (make sure B1 matches the top-left cell of your selected range, and $A1 points to your standard date column).
   • Set your desired highlight format (e.g., yellow fill) and click OK.

This rule will automatically adjust to every column in your selection—each cell will compare its extracted date to the corresponding row in your standard date column.

内容的提问来源于stack exchange，提问作者Benj