On Tue, 17 Feb 2009 04:06:03 -0800, FangYR
wrote:

Ok David,
I open a new workbook.
1) Format A2 as Date (14-Mar-01, as in dialogue box).
2) Insert formula in G2 which gives a number 693596 (A2 no data yet).
3) Type 1/3 in A2 and it reads 3-Jan-09.

The above is the simpliest way to state my case.
As I said earlier, it worked last year when I got this formula from Ron.

--
Regards
FangYR
Malaysia

If you think that the formula in G2 was in any way affecting your entry in A2,
you are mistaken.

If you want to make a month/day entry in A2, and have it change to a
month/day/year for 2008, you will need a VB solution.

You could try this:

Right click on the sheet tab.
Select View Code.
In the window that opens, paste the code below.
Read the notes in the code to understand better.

=====================================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range, c As Range
Dim lCurYear As Long

'As written, this will process all cells in column A change the
'current year to the previous.
'this range can be edited to affect only the desired range

Set AOI = Range("A:A")

If Not Intersect(Target, AOI) Is Nothing Then
Application.EnableEvents = False

For Each c In Intersect(Target, AOI)
If IsDate(c) Then
lCurYear = Year(Date)

'if year entered is this year, subtract one year.
'otherwise, leave the year entered unchanged
'this will make it impossible to enter dates in the current year
'if they are located in the range specified by AOI

If Year(c.Value) = lCurYear Then
c.Value = DateSerial(lCurYear - 1, _
Month(c.Value), Day(c.Value))
c.NumberFormat = "d-mmm-yyyy"
End If
End If
Next c
End If
Application.EnableEvents = True
End Sub
======================================
--ron