Is it possible to have excel highlight cells that don't conform to my desired format (ex. date as 6.2.2021 vs the desired 6/2/2021)?

Question

I use a spreadsheet to prioritize workflow for my team. Certain cells highlight depending on how close we are to cycle times. There is a start date, a tentative finish date, and a discharge date.

A blank discharge date cell turns red if: start date is not blank; tentative is not blank; and today's date is within five days of the tentative date.

Some of my team member have the habit of writing their dates with periods (mm.dd.yyyy). Excel of course does not recognize this as a date. BUT it does know that the 'tentative date' cell is not blank. As a result, even if today's date is equal to the tentative date, the 'discharge date' cell does not turn red. This is taking away from the utility of this sheet, as it is intended to be info at a glance, where a glaring red cell lets one know, "Oh shoot! I need to focus on that task as a priority."

I'd like for the start date and tentative date cells to go red if the date is not the desired format (mm/dd/yyyy).

Formula and condition in S1:

S1 fills red if

=AND($P1-TODAY()<=5,COUNTA($S1)=0,$M1<>0,$P1<>0)

Answer 1

In the same way that you can conditionally format the output as red with your supplied formula above, you can conditionally format the inputs (start date and tentative date) if they do not match your criteria.

One idea is to use something like =SEARCH(".", A1)>0 as the criteria for the format (if the incorrect input always comes in some variation of dd.mm.yyyy, and assuming the start of your range is in cell A1).

Here are the steps to do so if you did not create the original rule:

1. Highlight the ranges of the start and tentative dates
2. Go Home->Conditional Formatting->New Rule->"Use formula to determine which cells to format"
3. Enter the formula above (where you are sure to remove absolute references)
4. Go Format->Fill->Choose Red and hit okay