Date Filling for Prior Year

[Mike Copeland]

I maintain my personal checkbook in an Excel (Office 2003)
spreadsheet, and (as usual) I have to add most of the entries for last
year's transactions this year (for US Income Tax preparation). One
annoying thing I face each year is that I can't use the "shorthand" date
entry: for any date this year I can omit the year, but doing that for
_last_year's_date_ fills out this year's "year" value.
Is that any way apart from changing the date on my computer to assign
last year's year for the shorthand? That is, I want to enter "5/14" and
have it expand to "05/14/2010", etc. Please advise. TIA

 

[Dave Peterson]

I've known people to change the system date to last year, do the data entry and
then change that system date to today.

If you do that, remember to change it back.

 

[Mike Copeland]

last year's year for the shorthand? That is, I want to enter "5/14" and

A little more bulletproof version of the above; will only subtract a year if the year entered is 2011 (or converted to 2011 by Excel)
============================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range
Set rg = Range("A:A") 'date entry column
If Not Intersect(Target, rg) Is Nothing Then
Application.EnableEvents = False
If Target.Count = 1 And IsDate(Target) And _
Year(Target) = Year(Date) Then
Target.Value = DateAdd("yyyy", -1, Target)

Perfect - thank you very much!

 

[Rick Rothstein]

A little more bulletproof version of the above; will only subtract a

year if the year entered is 2011 (or converted to 2011 by Excel)

============================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range
Set rg = Range("A:A") 'date entry column
If Not Intersect(Target, rg) Is Nothing Then
Application.EnableEvents = False
If Target.Count = 1 And IsDate(Target) And _
Year(Target) = Year(Date) Then
Target.Value = DateAdd("yyyy", -1, Target)
End If
End If
Application.EnableEvents = True
End Sub
================================

Actually, that code will crash if the user enters a non-number into a cell
in Column A by mistake (try entering ABC for example). I think this slight
modification to your code will be bulletproof in the way you wanted...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range
Set rg = Range("A:A") 'date entry column
If Not Intersect(Target, rg) Is Nothing Then
Application.EnableEvents = False
If Target.Count = 1 And IsDate(Target) Then
If Year(Target) = Year(Date) Then
Target.Value = DateAdd("yyyy", -1, Target)
End If
End If
End If
Application.EnableEvents = True
End Sub

Rick Rothstein (MVP - Excel)