A very clever suggestion ! I'll keep it for my personal toolbox.
--
Gary''s Student - gsnu200741


"Sandy Mann" wrote:

Gary"s Student,

If youwill forgive a sugestion for alteration to your code, form the OP:

Is there any way of getting excel to accept post 1999 years entered as yy?

To accommodate date per 1999:

Option Explicit
Sub date_maker()
Dim dd As Date
Dim r As Range
Dim v As String
Dim Century As Long

Century = 2000

For Each r In Selection
With r
v = .Value
If Right(v, 2) 30 Then Century = 1900
dd = DateSerial(Century + Right(v, 2), Left(v, 2), 1)
.Clear
.NumberFormat = "mm/yy"
.Value2 = dd
End With
Next
End Sub

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Gary''s Student" wrote in message
...
Format the cells as Text when the responders enter data into the
electronic
questionnaire. Then use a small macro to change the text into a true date
when the results are analyzed:

Sub date_maker()
Dim dd As Date
For Each r In Selection
With r
v = .Value
dd = DateSerial(2000 + Right(v, 2), Left(v, 2), 1)
.Clear
.NumberFormat = "mm/yy"
.Value = dd
End With
Next
End Sub

--
Gary''s Student - gsnu200741


"dabarr" wrote:

The cell format has be set as a Custom format mm/yy.

When entering dates into cells as mm/yy Excel interprets 12/96 as
01/12/96
(dd/mm/yy English format) BUT if you enter 12/06 it enterprets it as
12/06/07
and displays it as 06/07. IE Excel is interpreting 12/06 as dd/mm and
defaulting the current year.

Is there any way of getting excel to accept post 1999 years entered as
yy?

This is part of an electronic questionnaire so the data entry has to be
completely idiot proof. I don't want to use yyyy or a format that needs
the
days. Nor do I want to use macros or dummy columns if I can help it.

Any thoughts most welcome!