Entering dates into cells as mm/yy or yy

[Guest]

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!

 

[Sandy Mann]

I am not privy to the inner workings of XL so the below is my interpretation
of what I get:

When presented with a "part date" like 12/96 XL tries to interpreit it into
a whole date by trying to add the current year. 12/96/2007 is obviously not
a real date so XL knows tries to make the date by adding day number 1 to
give 1/12/1996

For ambiguous dates like your 12/06 which *could* be 1/12/1906, 1/12/2006,
or 12/6/2007 XL goes through it procedure of adding the current year then a
day until is finds a acceptable date and the first one that it comes across
is 12/06/2007.

The only answer to ambiguous dates is to make them specific so that there is
no doubt what it was that you intended and that needs yyy or VBA

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

A very clever suggestion ! I'll keep it for my personal toolbox.