Forum Discussion

Dfuhrman8's avatar
Dfuhrman8
Copper Contributor
Jun 08, 2022

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. But I am having problems getting a conditional formula for the date not being formatted correctly or missing dashes or slashes. Would a vba script be better to use?

Formulas and Functions

3 Replies

Sort By
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jun 08, 2022
    why not let excel determine if it is a proper formatted date string:
    =ISNUMBER( DATEVALUE( A1 ) )
    • Dfuhrman8's avatar
      Dfuhrman8
      Copper Contributor
      Jun 08, 2022
      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.
      • mtarler's avatar
        mtarler
        Silver Contributor
        Jun 08, 2022
        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.

Resources