0

When trying to export data from Access database files to SQL server 2012, I am receiving an error of bad data. Upon investigation, I found the table and the column, which was causing an error.

The column contains data of date 'mm/dd/yyyy' format, and it seems like there is some bad entries.

Currently I am trying to find faulty data, but there are 4000+ rows of data. As way out, I decided to use excel to use its conditional formatting. However, I couldn't find/create a rule which would suit my needs.

So I want to ask before I move to looking for something else: Is there a way (formula or a rule) to highlight cells which do not follow proper formatting?

For example, I need exactly the format 'mm/dd/yyyy', so 01/01/2016 will be correct, while 1/1/2016 or 01/01/16 would be highlighted.

Vadzim Savenok
  • 930
  • 3
  • 14
  • 37
  • You might want to read this: http://stackoverflow.com/questions/37100821/change-date-format-using-substitute-or-replace/37101358#37101358 AND this: http://stackoverflow.com/questions/38000194/difference-between-date-and-time-w-out-work-week-excel/38001028#38001028 Afterwards, you'll come to realize that **all** dates in Excel are just numbers. So, you merely need to check if all of these cells contain numbers. That's an easy validation rule. Yet, if you have merely text which is formatted like dates then you're out of luck and need some VBA programming to accomplish that. – Ralph Oct 28 '16 at 16:02
  • @Ralph In excel, it is a custom format mm/dd/yyyy, so I would assume that's the text. Then I seem to be out of luck here. Still will give it a read though, thanks. – Vadzim Savenok Oct 28 '16 at 16:05
  • Text-dates are a mess. There are Excel formulas (such as `DATEVALUE`) and vba functions (such as `CDate`) which try to convert text-dates to real dates (numbers formatted as dates). Yet, they can be wrong and in my corporate environment I came to understand that it's best to parse all text-strings and convert them yourself ("manually") to a real date and then validate the result (making sure you don't get dates out of range such as a date in the year 1921 maybe). – Ralph Oct 28 '16 at 16:12
  • @Ralph Seems like a lot of unnecessary work. I will look for some alternative solution then. – Vadzim Savenok Oct 28 '16 at 16:14

1 Answers1

1

With "dates" in column A, this little sub will mark text cells in yellow and date cells with the wrong format in green:

Sub DateCheck()
    Dim r As Range, s As String, DQ As String
    DQ = Chr(34)
    For Each r In Intersect(Range("A:A"), ActiveSheet.UsedRange)
        If r.Value <> "" Then
            s = Evaluate("Cell(" & DQ & "type" & DQ & "," & r.Address(0, 0) & ")")
            If s = "l" Then
                r.Interior.Color = vbYellow
            ElseIf r.NumberFormat <> "mm/dd/yyyy" Then
                r.Interior.Color = vbGreen
            End If
        End If
    Next r
End Sub

enter image description here

Gary's Student
  • 95,722
  • 10
  • 59
  • 99