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.

Answer 4

I'm leaving this here for anyone, like myself, who was helped by JMax's answer. Ths version works on all sheets in a workbook (and thus must be placed into the workbook's script, not a worksheet's) and also only changes entries which contain the current year. Values for the current year and for the last year are set automatically.

Dim This_Year As Integer
Dim Last_Year As Integer

Private Sub Workbook_Open()
    This_Year = Year(Date)
    Last_Year = This_Year - 1
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Intersect(Target, [A:A]) Is Nothing Then Exit Sub
    If IsDate(Target) And Year(Target) = This_Year Then
        Application.EnableEvents = False
        Target.Value = DateSerial(Last_Year, Month(Target), Day(Target))
        Application.EnableEvents = True
    End If
End Sub

Answer 5

• 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.

Answer 6

Thanks for the answers - I was having this same issue. I wanted to make a slight amendment to Stewbob's solution. Like the OP I needed to enter data from last year without having to type "/11" each time, but not all of the dates I was entering were in 2011 (I was also entering dates in 2009 and 2010, for which I would have to manually enter the year anyway even if I hadn't procrastinated in 2011!). I would not want dates in '09-'10 to be set back by a year along with the 2011 dates. My solution was to set up a second adjacent column with the following formula. Let's say I'm typing dates into column A and made column B my "corrected" column; the formula in column B is: =IF(A1>40908,IF(A1>40967,A1-366,A1-365),A1) This way, dates before 2011 will not be corrected, and we also account for the fact that 2012 is a leap year so any dates after 2/28 will be set back by 366 days instead of 365. So now, I can type any dates as if it's still 2011. This is nice if you can't change the date/time in Windows because your institution doesn't give you administrative privileges on your own #$%! workstation. It may be a few days after the original post but I hope this helps.

Answer 7

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.)

Answer 8

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