Share via

Excel Formula for Highlighting Certain Dates

Dicden 0 Reputation points
2026-04-27T11:32:37.8566667+00:00

Hello,

We are in need to create an excel formula to highlight a cell in following conditions:

In our sheet, a sample's arrival date goes to column B. Referring to the date in the "B" cell in its respective row, we need cell "O" to get filled in red, if it has been more than more than 7 days. But our file is a very old file that was created approx. 10 years ago. So we only need this to rule to apply below a certain cell (after around 22000), as this will be a new regulation.

When I try to set =TODAY()-$B2>7 as a new rule for the "O" column (or $B22105), all the empty cells also get filled. So, then I try =IF(AND(TODAY()-$B2>7,$O2<>""), and I end up with an error. I will appreciate any help you may provide. Thank you.

Microsoft 365 and Office | Excel | Other | Windows

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Katerina-N 9,075 Reputation points Microsoft External Staff Moderator
    2026-04-30T21:11:58.0166667+00:00

    Hello Dicden,

    I can see you have issue with your conditional formatting formula. The problem is that your formula is checking if column O is not empty, but you need to also ensure the date in column B exists.

    You can try to use this formula instead:

    =AND(TODAY()-$B2>7;$B2<>"";ROW()>22105)

    What this does:

    • TODAY()-$B2>7 - Checks if more than 7 days have passed
    • $B2<>"" - Ensures column B has a date (not empty)
    • ROW()>22105 - Only applies the rule to rows after row 22105 (your cutoff point)

    To apply it:

    1. Select the range in column O where you want this rule (e.g., O22106:O1000000)
    2. Go to Home > Conditional Formatting > New Rule
    3. Choose "Use a formula to determine which cells to format"
    4. Paste the formula above
    5. Set the format to red fill
    6. Click OK

    The key difference from your attempt is adding the $B2<>"" check AND the ROW()>22105 condition to limit it to your new data only. This prevents empty cells from being highlighted and ensures the rule only applies below row 22105.

    Thank you for your patience and your understanding. If you have any questions, please feel free to reach out.

    I'm looking forward for your reply.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Was this answer helpful?

    0 comments
  2. Q&A Assist
    2026-04-27T11:32:52.8+00:00
    AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

    Use a conditional formatting formula that:

    1. Only applies from the first “new regulation” row (e.g. row 22001), and
    2. Checks that column B has a date and that it is more than 7 days before today.

    Example setup (adjust row numbers as needed):

    1. Select the range in column O where the rule should apply, e.g. O22001:O1048576.
    2. On the Home tab, select Conditional Formatting > New Rule.
    3. Choose Use a formula to determine which cells to format.
    4. Enter this formula (assuming the first row in the range is row 22001): 
         =AND($B22001<>"",TODAY()-$B22001>7)
      Explanation:
      • $B22001<>"" ensures the rule only applies when there is a date in column B (so empty rows in B do not format O).
      • TODAY()-$B22001>7 checks if the date in column B is more than 7 days ago.
    5. Click Format, choose a red fill, and click OK.
    6. Confirm the rule and ensure the Applies to range is =$O$22001:$O$1048576 (or whatever range is needed).

    Key points:

    • Do not wrap the formula in IF for conditional formatting; use AND directly, as conditional formatting treats a TRUE result as “apply format”.
    • Make sure the formula references the first row of the Applies to range (Excel will adjust it for each row).

    References:

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.