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

Question

Context: I'm entering prior year data into Excel.

Every time I type in the date in the date column ("9/16" for September 16th), Excel automatically formats it to "9/16/12", where 12 is 2012, the current year.

I'm entering data from last year in the current year 2012. I don't want to type the "11" for 2011. I want Excel to automatically populate it as it does with 2012, and as it did on December 31st.

The simplest fix is to set the clock in Windows back to any time in 2011, but that tends to muck with the network which wants to set me back and complains about my network password being out of date, etc.

I prefer the date to reside in a single column, so tabbing to alternate columns for day/month/year is not an option for me.

One would think this is a simple fix, but a couple hours searching and my Google-fu is failing me.

Answer 1

Create a new cell with the below formula

=DATE(YEAR(B2)-1,MONTH(B2),DAY(B2))

and the copy just the value in the original cell (B2)

Answer 2

1. Select the column containing the dates
2. Right click at the top and select "Format cells"
3. Select "Custom"
4. In the "Type" window enter the desired date format but specify the year you want to appear. In my case, I needed the dates to be in 2018 but I'm entering data in 2019. So I did the above steps to set a custom format of:

mm/dd/"2018"

It works like a charm without having to use code.

Answer 3

Simple fix is You just enter date and month and let the system enter the default date. After that replace (Ctrl+H) 2012 with 2011.