Auto populate with current, past, or upcoming text based on year

hdierkers

I am trying to auto populate a column with "Current", "Past", or "Upcoming" based on the year column. So for a row with 2023, I would want "Current", but would want "Past" or "Upcoming" based on what's in the Year column. I've put in the following formula but get an error (#UNPARSEABLE)

=IF(Year@row = YEAR(TODAY()), "Current", IF(Year@row=Year(today())-1,"Past",IF(Year@row=YEAR(today())+1,Upcoming,0)

Carson Penticuff

See if this works for you:

=IF(Year@row = YEAR(TODAY()), "Current", IF(Year@row < Year(today()),"Past", IF(Year@row > YEAR(today()), "Upcoming", 0)))

Carson Penticuff

It looks like you missed a couple closing parentheses and the quotes around "Upcoming".

=IF(Year@row = YEAR(TODAY()), "Current", IF(Year@row = Year(today()) - 1,"Past", IF(Year@row = YEAR(today()) + 1, "Upcoming", 0)))

hdierkers

So that worked when it's one year in the future or past, but does not track after 2022 (2 years back from today). Any way to get all years in the past to calculate? If it helps, the data I have goes back to 2011, but I will be tracking future projects as well (currently out to 2025).

Carson Penticuff

See if this works for you:

=IF(Year@row = YEAR(TODAY()), "Current", IF(Year@row < Year(today()),"Past", IF(Year@row > YEAR(today()), "Upcoming", 0)))

hdierkers

Yes! Thank you!

hdierkers

@Carson Penticuff - wanting to take this a step further, and calculate data for completed projects from the last 3 years. I've tried the formula below, but I keep getting and INVALID DATA TYPE error.

=COUNTIFS({LEGACY Submissions Project List Project Type}, ="Project Type", {LEGACY Submissions Project List Status}, ="Completed", OR({LEGACY Submissions Project List Year}, =YEAR(TODAY()) - 3, =YEAR(TODAY()) - 2, =YEAR(TODAY()) - 1))

Carson Penticuff

I see a couple of options. I think this will fix what you are attempting to do:

=COUNTIFS({LEGACY Submissions Project List Project Type}, = "Project Type", {LEGACY Submissions Project List Status}, = "Completed", {LEGACY Submissions Project List Year}, OR(@cell = YEAR(TODAY()) - 3, @cell = YEAR(TODAY()) - 2, @cell = YEAR(TODAY()) - 1))

You could also simplify this a little, I believe. This should work assuming you do not have anything marked as complete with a date from a future year:

=COUNTIFS({LEGACY Submissions Project List Project Type}, = "Project Type", {LEGACY Submissions Project List Status}, = "Completed", {LEGACY Submissions Project List Year}, >= YEAR(TODAY()) - 3)

hdierkers

The first one worked - thanks!

Carson Penticuff

Awesome, I'm glad I could help!