Auto populate with current, past, or upcoming text based on year
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)
Best Answer
-
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)))
Answers
-
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)))
-
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).
-
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)))
-
Yes! Thank you!
-
@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))
-
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)
-
The first one worked - thanks!
-
Awesome, I'm glad I could help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!