Display Year in a 10-Year Cycle
I have a sheet that is used to track where a store is at in a remodel cycle. Currently, I just have a column that takes the later date of two date columns, and adds ten years for an estimated remodel date. I'd like to add another column that displays the year in that 10-year cycle.
In this sheet the Future AF column takes the earlier date, and adds 10-years. Row 1 takes Latest BIR because that's the earlier date. The AF Counter displays 2, because we are in the 2nd year in the 10-year cycle. 2020 - 2018 = 2.
Can anyone assist with figuring out this formula to display this counter accurately? If a Next AF date is not available, it should read "Missing Data".
Thank you
Best Answers
-
Try something like this...
=YEAR(TODAY()) - YEAR(MAX([Latest AF]@row, [Latest BIR]@row)) + IF(AND(MONTH(TODAY()) >= MONTH(MAX([Latest AF]@row, [Latest BIR]@row)), DAY(TODAY()) >= DAY(MAX([Latest AF]@row, [Latest BIR]@row))), 1)
-
Give this a whirl...
=IF([Latest AF]@row = "", "Missing Data", YEAR(TODAY()) - YEAR(MAX([Latest AF]@row, [Latest BIR]@row)) + IF(AND(MONTH(TODAY()) >= MONTH(MAX([Latest AF]@row, [Latest BIR]@row)), DAY(TODAY()) >= DAY(MAX([Latest AF]@row, [Latest BIR]@row))), 1))
Answers
-
Good afternoon,
For the Future AF column try:
= IFERROR(date(YEAR(max(Last AF@row, Last BIR@row))+10, month(max(Last AF@row, Last BIR@row)), DAY(max(Last AF@row, Last BIR@row)))),"")
For the AF year formula use =iferror(YEAR(Future AF),"")
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hi Mark,
I already have the Future AF formula figured out to display 10+ years on the earlier date of the two prior columns. The AF Cycle column is the formula I need help with.
Thanks
-
so you want a formula to count where you are in the current cycle and the result should show up in the column "AF Cycle"?
Question 1, because it looks a little mixed up: Next AF = Future AF?
Question 2: Do you only care about YEAR, or about real 12 month cycles?
Basic logic: if "Latest BIR" is smaller than "Latest AF", then subtract YEAR "Latest AF" from YEAR "Future AF", else subtract YEAR "Latest BIR" from YEAR "Future AF".
If you want to count real 12 month (or 365day) cycles then you need to subtract whole dates, divide the result by 365 and round up/down to your liking.
Hope this helps
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
Hi @Stefan,
Correct, the AF Cycle column should show the cycle year counter.
Question 1: Looks like I mixed up Next AF and Future AF in my original post, you are correct that Future AF is the same thing and the name I'm using for the column.
Question 2: Year would be okay, however a 12-month cycle would be even better and more accurate. That would be preferred if possible.
Basic Logic: You nailed the basic logic! If only one date column is filled in, it should take that date. If no dates are available, can it display "Missing Data"?
Thank you so much!
-
You could try something like this for the year cycle...
=YEAR(TODAY()) - YEAR([Latest AF]@row + IF(AND(MONTH(TODAY()) >= MONTH([Latest AF]@row, DAY(TODAY()) >= DAY([Latest AF}@row)), 1)
-
Getting an #UNPARSEABLE error with this one.
-
My apologies. Those parenthesis got away from me.
=YEAR(TODAY()) - YEAR([Latest AF]@row) + IF(AND(MONTH(TODAY()) >= MONTH([Latest AF]@row), DAY(TODAY()) >= DAY([Latest AF]@row)), 1)
-
That seems to work however, I believe it's only pulling the date from the Latest AF column. How can we make it so it pulls the later date from Latest AF and Latest BR?
"if "Latest BIR" is smaller than "Latest AF", then subtract YEAR "Latest AF" from YEAR "Future AF", else subtract YEAR "Latest BIR" from YEAR "Future AF"."
-
Try something like this...
=YEAR(TODAY()) - YEAR(MAX([Latest AF]@row, [Latest BIR]@row)) + IF(AND(MONTH(TODAY()) >= MONTH(MAX([Latest AF]@row, [Latest BIR]@row)), DAY(TODAY()) >= DAY(MAX([Latest AF]@row, [Latest BIR]@row))), 1)
-
That seems to have worked!
Any way we can add to this formula so that if Latest AF and Latest BIR are missing dates, it'll display "Missing Data"? Another way of doing it could be if Future AF is missing a date, it'll display the Missing Data.
Thank you!
-
Give this a whirl...
=IF([Latest AF]@row = "", "Missing Data", YEAR(TODAY()) - YEAR(MAX([Latest AF]@row, [Latest BIR]@row)) + IF(AND(MONTH(TODAY()) >= MONTH(MAX([Latest AF]@row, [Latest BIR]@row)), DAY(TODAY()) >= DAY(MAX([Latest AF]@row, [Latest BIR]@row))), 1))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
