Display Different year from current

Topic

New Reply

If you type in just the month and day, for instance, 3/15, Excel adds the current year.

I am doing a (huge) list that involves a previous year. Is there any way I can get Excel to fill in the proper year if I only add the month and day. (My current way of doing it is to do the month and day, and then when all is done, I do a search for /2012 and replace it with /2010. It works, and is not too much trouble except when I miss some of the substitutions.

Just asking.

Reply | Quote

Replies

The only way I know is by changing your system clock !

That will work, but may give some problems with, for instance, your AV program – at last until you change it back.

Reply | Quote

The only way I know is by changing your system clock !

That will work, but may give some problems with, for instance, your AV program – at last until you change it back.

Giving Troubles it does (with apologies to Yoda). I just thought I would ask in case someone had stumbled upon something.

Reply | Quote

Add an extra column that calculates the year and use that to check you have changed everything.
You could use the extra column to subtract 365 days from the value and then copy / paste special, values.

cheers, Paul

Reply | Quote

For entering many dates, I often use 3 intermediate columns: month, day, year and then use date function to calculate it. After entry I paste – special- values.

Steve

Reply | Quote

Hi

You could use a worksheet change event to set the required year.

The following code checks for entries made in column 2 only (adjust code as required).
The following code sets the year to 2010. (adjust code as required).

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column 2 Then Exit Sub
zEntry = Target.Value
If IsDate(zEntry) Then
Application.EnableEvents = False
zAddress = Target.Cells(1).Address
zDay = Day(zEntry)
zMonth = Month(zEntry)
zYear = 2010
Range(zAddress).Value = DateSerial(zYear, zMonth, zDay)
End If
Application.EnableEvents = True
End Sub

zeddy

Reply | Quote