Validate YYYY/MM/DD with conditional formatting

Welcome to the forum.

If the cell being pasted into is pre-formatted using the date format you want, then any date which is a valid date should automatically display in that format. If it's not pre-formatted, then any valid date pasted in will take on the default short date format.
A 'valid date' is anything which is valid according to your regional settings, which are probably set to the USA mm-dd-yyyy format. For example, any of these would be treated as valid:
2019/07/24, 07/24/2019, 7-24-19, 07 24 19, etc.
The only way I know of to get dates to not appear as dates is to pre-format the cell as text, which forces the pasted text to go in exactly as pasted.

However, if you still need/want a Conditional Formatting formula, then try this:

Formula:

Please Login or Register  to view this content.

You should also use a second one to catch any non-valid date formats:

Formula:

Please Login or Register  to view this content.

If there's a chance that pasted dates might have leading spaces, then amend the first formula to this:

Formula:

Please Login or Register  to view this content.

Hope that helps.


Edit: as noted in the post below, this Conditional Formatting will only work if the users are pasting 'values' in, otherwise the formatting, including the CF, will be over-written. If that's going to be the case, then you'll need to highlight 'errors' in another cell which the users can't overwrite.

Originally Posted by dukefan3417

Hello,
I am trying to build a validation template that users will paste their data into. The idea is if they paste "2019/07/24" (YYYY/MM/DD), then the cell will do nothing. If they post any other date format, then it will highlight yellow. I am not trying to convert it to the correct format, just to display which ones are not correct. I would prefer a formula as opposed to VBA, if possible.

As a last resort, I would even take a formula that would highlight if the cell does not begin with "2019/".

Thanks!

Highlighting like you want, requires Conditional Formatting (which you probably already knew)
1. Copy/pasting into a cell with CF, removes the original formatting/CF, so, unless the source cell contains that CF rule, this is not going to work for you, the way you want it.
2. A date in excel is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Thu 25 Jul 2019) is actually 43671
As long as the info being copied is a "real" date, the actual formatting wont matter to excel, as all it will rea;;y "see" is that 5-digit value

Aardigspook thanks for the rep