Excel 2007 and date format

[picklefactory]

Hi folks
Not sure if I'm just being dumb here. (Probably), but......I have simple column of dates, I have set format to dd/mm/yy. Am I correct in thinking this should force dates entered into this format? Reason I ask is that I'm trying to create a sheet to make people keep to the correct syntax. But even with cells formatted as above, I can still enter 04.07.08 or 04.07/08 or 04/07.08 or 04.07.2008 and other variants and they remain as typed. Shouldn't the formatting sort this out and fix the syntax to the format I selected?
Thanks

 

[GlennUK]

No, that's not the way formatting works. Formatting controls the way cell entries are displayed, according to what the cell contents are. Is doesn't control what is entered as contents.

Have a look at Data Validation.

 

[Norie]

None of those values are dates and formatting won't have any effect on a value in a cell.

Unless that's a new feature in 2007.

 

[VoG]

Try also applying Data > Data Validation > Data Validation, select Date and set Greater Than 01/01/1900 (or another valid date).

 

[picklefactory]

Thanks Glenn
Not sure my explanation was very good. It's only display that concerns me, I don't mind that I can enter the data in various forms, but it is still displaying it exactly as typed. Does that make sense, I know what I mean and you should be able to read my mind.
Thanks again

 

[njimack]

If you enter a number containing a decimal point, Excel has no way of converting this into a date. Formatting simply affects the way the cell is displayed. Try this code in the sheet module (assumes the cell in question is A1)

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    If Target.Address = "$A$1" Then
        If Not IsDate(Target.Value) Then
            MsgBox "Wrong value entered"
            Target.ClearContents
        End If
    End If
Application.EnableEvents = True
End Sub

 

[picklefactory]

The data validation route doesn't quite give me what I'm after. It generates a warning and forces the user to try again (Which helps) but I'm looking for the cell to sort out the format that is displayed, rather than the user. Is that going to be a VBA function?

 

[picklefactory]

njimack
Thank you for that, but am I right in thinking that will just generate a warning and refuse to accept the date as typed if not in date format? That would be the same result as I had with data validation, again, not what I'm after. Thanks though, anyway.

 

[njimack]

Something like this maybe?

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Dim dtyr, dtmth, dtday
    If Target.Address = "$A$1" Then
        With Target
            dtyr = Right(.Value, 2)
            dtmth = Mid(.Value, 4, 2)
            dtday = Left(.Value, 2)
            .Value = dtday & "-" & dtmth & "-" & dtyr
        End With
    End If
    Application.EnableEvents = True
End Sub

 

[picklefactory]

Norie
You've nailed the root of my problem, told you I was probably being dumb............again. Excel was simply not recognising the data entered as a date, why did I miss that????

njimack
I'll have a dabble with that code and see what it does for me, and post back on Monday (Weekend has just arrived at my desk.....Woohoo!)
Thanks for your time folks, have a good weekend.