Excel does NOT recognize my Date Format

I was hoping that there would be a way to 'convert' Excel to adopt this? Even if it was on a sheet by sheet basis....

Thank you for your assistance thus far.

-Jeremy

Well, if you're open to using a Visual Basic solution, you can set it up so that whenever one of your users enters a date in the DDMMYY format in specific cells, then a VBA routine is initiated that will automatically convert the cell contents to an actual date, or to a text string in the form "DD-MM-YY".

There is no way to get Excel to store an input value of "010577" as a date - Excel uses its own internal format for dates, and it would look more like 28130.

The code below can be inserted into each worksheet's code module which you get to by right-clicking on the worksheet tab and selecting "View Code".  Any time the user makes a change on the worksheet, this code will see if it changed anything in Column J (where your hand input dates are).  It checks for illegal changes, and then converts whatever was input into a text string in the "DD-MM-YY" format.

Stick it into one of your worksheets, and try some entries into Column J to see if it works correctly.  Is this something you want to try?

'===== BEGIN CODE =====

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim dateRange As Range, intRange As Range

    Dim theCell As Range

    Dim disAllow As Boolean

    Dim myDate As Date

' Set up a "range of interest" to check for incoming dates

    Set dateRange = Me.Range("J2:J50000")

' See if the changed cells on the worksheet intersect our range of interest

    Set intRange = Intersect(dateRange, Target)

' Some of our cells changed...

    If (Not intRange Is Nothing) Then

'

' Before we do anything, check for disallowed changes.

' If we find any disallowed changes, Undo the action.

' Erasing cell contents is allowed.

' Putting values in that cannot be converted to

' a date is not allowed - this will result in an Undo.

'

        disAllow = False

        For Each theCell In intRange.Cells

            If (theCell <> "") Then

                If (Not IsLawsonDate(theCell.Value)) Then ' If we find one cell input incorrectly, undo the whole action

                    MsgBox "That change is not allowed because it produces a bad date in one or more cells!", vbExclamation + vbOKOnly, "Error!"

                    Application.EnableEvents = False

                    Application.Undo

                    Application.EnableEvents = True

                    Exit Sub

                End If

            End If

        Next theCell

'

' If we got here, the user action was legal, so just go ahead and convert the date(s).

'

        For Each theCell In intRange.Cells

            If (theCell <> "") Then

                Call ConvertLawsonDate(theCell.Text, myDate)

                Application.EnableEvents = False

                theCell = Format(myDate, "DD-MM-YY")

                Application.EnableEvents = True

            End If

        Next theCell

    End If

End Sub

Private Function IsLawsonDate(ByVal inputString As String) As Boolean

    Dim i1 As Integer, i2 As Integer, i3 As Integer

    Dim returndate As Date

'

' Lawson date format = "DDMMYY"

'

    IsLawsonDate = True

Debug.Print "hi"

    If (Len(inputString) = 5) Then inputString = "0" & inputString

    If (Len(inputString) <> 6 Or Not IsNumeric(inputString)) Then

        IsLawsonDate = False

    Else

        i1 = Left(inputString, 2)

        i2 = Mid(inputString, 3, 2)

        i3 = Right(inputString, 2)

        If (i1 < 1 Or i1 > 31 Or _

            i2 < 1 Or i2 > 12 Or _

            i3 < 1) Then IsLawsonDate = False

        On Error GoTo NotLawsonDate

        returndate = DateSerial(i3, i2, i1)

    End If

    Exit Function

'

NotLawsonDate:

    IsLawsonDate = False

End Function

Private Sub ConvertLawsonDate(ByVal inputString As String, ByRef returndate As Date)

    Dim i1 As Integer, i2 As Integer, i3 As Integer

'

' Lawson date format = "DDMMYY"

'

    If (Len(inputString) = 5) Then inputString = "0" & inputString

    i1 = Left(inputString, 2)

    i2 = Mid(inputString, 3, 2)

    i3 = Right(inputString, 2)

    On Error GoTo NotLawsonDate

    returndate = DateSerial(i3, i2, i1)

    Exit Sub

'

NotLawsonDate:

    returndate = DateSerial(1900, 1, 1)

End Sub

'===== END CODE =====