Share via

Conditional Formatting for Dates with Rules

Anonymous
2024-09-16T17:05:11+00:00

My spreadsheet has a promise date (O) and an actual ship date (AH). I want to highlight the promise date if it was shipped after this date, however:

-If the item has not shipped, it will populate "1/0/00" and should not be highlighted.

-If the promise date and ship date are equal, it should not be highlighted.

I've tried IF and AND in Conditional Formatting, but my formulas aren't working so I must be missing something.

So for example, rows 1-7 would not highlight, but the rest would.

Any ideas?

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. HansV 462.6K Reputation points
    2024-09-16T18:19:19+00:00

    The ship date is not after the promised date in any of the rows (if we exclude 1/0/00), so none of the cells should be highlighted.

    Select O2:O100 or however far down as appropriate.

    O2 should be the active cell in the selection.

    On the Home tab of the ribbon, click Conditional Formatting > New Rule...
    Select 'Format only cells that contain'.
    Leave the first drop down set to 'Cell Value'.
    Select 'less than' from the second drop down.
    In the box next to it, enter the formula

    =AH2

    Click Format...
    Activate the Fill tab.
    Select yellow as fill color.
    Click OK, then click OK again.

    Was this answer helpful?

    0 comments