Excel 2007 and date format

picklefactory

Active Member
Joined
Jan 28, 2005
Messages
412
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,536
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
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.:eek:
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try also applying Data > Data Validation > Data Validation, select Date and set Greater Than 01/01/1900 (or another valid date).
 

picklefactory

Active Member
Joined
Jan 28, 2005
Messages
412

ADVERTISEMENT

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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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)

Code:
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

Active Member
Joined
Jan 28, 2005
Messages
412

ADVERTISEMENT

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

Active Member
Joined
Jan 28, 2005
Messages
412
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Something like this maybe?

Code:
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

Active Member
Joined
Jan 28, 2005
Messages
412
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top