How to force Excel to automatically fill prior year in column instead of current year?

A quick fix is to just enter 9/16 and let Excel change it to 9/16/12. Then when you are all done entering your dates, in a new column, enter the formula =A1-365, and just copy the formula all the way down, assuming column A contains the dates that you entered.

One watch-out on this: 2012 is a leap year, so for any dates after Feb 28 that you enter, your formula will need to be =A1-365.25 or else your dates will be off by one day. It doesn't have to be 365.25, just something larger than 365 and smaller than 366.

This works because no matter what the date format in the cell is, Excel stores the actual date as the number of elapsed days since January 0, 1900. (Yeah, January 0 isn't a real date, but Excel thinks it is.)

What you found is correct, you cannot change the default Excel parameter about date. Apart from changing the system date, you can use a worksheet event formula :

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [A:A]) Is Nothing Then Exit Sub
If IsDate(Target) Then
    Application.EnableEvents = False
    Target.Value = DateSerial(2011, Month(Target), Day(Target))
    Application.EnableEvents = True
End If
End Sub

• Highlight the column of dates in your Excel spreadsheet where you wish to change the year.
• In the toolbar, select Format, select Cells, and under Category, select Date. In the column of options it offers for date format, select the date format that shows month spelled out, day number, comma, space, 4-digit year (i.e.: January 31, 2011). Be sure there is a space before the 4-digit year.
• Find the "Replace" command under Edit menu (location varies depending upon Mac or PC and Excel version #)
• In the Replace window, in the field for the data you wish to replace, enter that 4-digit year that you want to replace. In the other field for the data you wish to replace the old data with, enter the correct year. Click the button in that window for "Replace All".
• Your highlighted column in the spreadsheet will now have changed all of the 4-digit years to the new number.
• If you didn't wish to change all of those years in that column, I think you could have just left some of them un-highlighted, and they would have been skipped over.
• Afterward, you can highlight the column, go back into Format, Cell, Date, and reset the date format back to whatever you like, such as 1/31/11.