Individual Calendar Year Per Worksheet

[default_name]

Hey!

I have finances laid out in Excel.

There are multiple worksheets in the workbook.
Each worksheet is for a particular year.

"2018"
"2019"
"2020" etc

Is there a way to assign a calendar year to each of these worksheets?
ie: I want to assign the year 2019 to the 2019 worksheet, so that entering dates will be quicker when within that worksheet.
If I am within the 2019 worksheet and I want to assign June 19th to a cell I would have to type 6/19/19 versus simply just typing 6/19.
Currently if I were to type 6/19 it would auto-fill with a 2018 year on it.

Hope that makes sense.
Thanks in advance!
You guys are great!

 

[Oscar Cronquist]

I don't think you can assign a calendar year to each worksheet, as far as I know. Excel uses the current year, you would need to change the current date for that to happen.

 

[chrisnash]

You can sort of do it using VBA.

If you do your dates as normal then add this code to thisworkbook (press Alt + F11, the click on thisworkbook)

Sub DateNew()


Dim yearsToadd As Variant
Dim NewYear As Variant
yearsToadd = InputBox("Number of years to add")


With ActiveSheet.Range("A1:A10")  'Change the range as required
    .Value = DateAdd("yyyy", yearsToadd, ActiveSheet.Range("A1").Value)
End With
End Sub

Then you can either assign the macro to a letter by hitting Alt and F8 then options

 

[chrisnash]

remember to save the workbook as either an xls or xlsm not xlsx.

You will then need to accept any security warnings regarding macros.

 

[default_name]

Thanks guys!!