Excel 97 macro query

P

PaulS

Can anyone help (or point me in the right direction) with
a macro that will automatically change a year entered into
a cell into day/month/year format. For example in column
A"1991" entered into a cell would automatically change to
01/01/1991, in column B "1991" entered into a cell would
automatically change to 31/12/1991.

The reasoning behind this is the spreadsheet is filtered
at a later date and only a small proportion of the data
originally input is transferred to a mastersheet. To input
dates in the dd/mm/yyyy layout at each entry would waste a
lot of time and effort

Many thanks for any advice you can give
 
B

Bob Phillips

Paul,

This worksheet event code should do it

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = True
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1,B1")) Is Nothing Then
If IsNumeric(Target.Value) Then
If Target.Address = "$A$1" Then
Target.Value = DateSerial(Target.Value, 1, 1)
Else
Target.Value = DateSerial(Target.Value, 12, 31)
End If
End If
End If

ws_exit:

Application.EnableEvents = True
End Sub

This goes into the worksheet code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

TEaslon

Why not first try to use menu Format, Cells, Custom and in
the "Type" space create the format if it is not already
there?
 
D

Dave Peterson

typo alert!

I think Bob wants that first:

Application.EnableEvents = True
to read
Application.EnableEvents = False

(He wants to turn it off so changing the cell doesn't fire the macro again.)
 
Top