Forum Discussion
Conditional Format or VBA Script
I have a report that I am needing to highlight the column that has dates in it, if the date is not formatted mm/dd/yyyy, blank, missing dashes or slashes. I have a conditional statement for blank. Bu...
I get a spreadsheet in that can list 1000's of accounts including DOB. If the dates are not formatted to mm/dd/yyyy. I have to highlight the field and end back to the client to fix. sometimes they blank, which I have a conditional formatting rule for that. They may just be a set of numbers: 121522. This is not a date formatted correctly. I was looking for something that I can set as a conditional format or even a vba script for a macro to run and check.
that is what I gave you. IF excel can recognize that text string as a valid date format it will then convert it to a date object/value/number but if not it will error and the ISNUMBER() will check if excel succeeded or not (you could also use =IFERROR( DATEVALUE( A1 ) , 0 )
Put the replace the A1 with the very top left cell of the 'Applied to' range of the conditional formatting. This assumed the data is being imported as text. If excel already recognizes the cells as date values we will have to add another part to the condition.
Put the replace the A1 with the very top left cell of the 'Applied to' range of the conditional formatting. This assumed the data is being imported as text. If excel already recognizes the cells as date values we will have to add another part to the condition.