CODEWITHSUNDEEP
excelvba
Matt Lane
Matt Lane

Reputation: 97

Defining current year and next year in code

How do I split up my data by current year and next year?

I will have one tab dedicated to current year (2018) and one dedicated to next year (2019).

My issue is that once January 1, 2019 hits, my code will be looking at 2019 as the current year and 2020 as the next year.

Two solutions I can come up with.

First, I format the Date Column for "YYYY" (ie. 2018). Then on a separate tab, I will have a cell box with the current year in it (can be changed each year). The countif formula will pull only items that have years equal to the Cell box.

Second. I have the date column set for "YYYY" on my spreadsheet. The column next to it will be set to the current year "YYYY". I will subtract the two in order to get "1's and 0's". Then my Countif formulas will look for "1's and 0's" in the subtraction column depending on which sheet you are looking at.

Is there a better idea?

Upvotes: 0

Views: 2676

Answers (2)

danield
danield

Reputation: 21

You could just show a user form that let's the user specify the relevant years for calculation via two date pickers or simple input boxes.

Or you might even stay away from Excel/VBA entirely and solve this by using a (e.g.) python script that uses your excel files (or some CSV) as source. This might be a good alternative if you don't really need VBA/Excels functionality or if it even limits you in some way, like creating reports.

Upvotes: 0

ArcherBird
ArcherBird

Reputation: 2134

Instead of hardcoding the year, you can grab the current year using:

Year(Now())

and next year would be :

Year(Now()) + 1

Upvotes: 1

Related Questions