Display Year in a 10-Year Cycle

Daniel Hollywood

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

Paul Newcome

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)

Paul Newcome

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))

Mark Cronk

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

Daniel Hollywood

https://community.smartsheet.com/discussion/comment/269139#Comment_269139

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

Stefan

Hi @Daniel Hollywood,

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

Daniel Hollywood

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!

Paul Newcome

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)

Daniel Hollywood

https://community.smartsheet.com/discussion/comment/269500#Comment_269500

Getting an #UNPARSEABLE error with this one.

Paul Newcome

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)

Daniel Hollywood

https://community.smartsheet.com/discussion/comment/269513#Comment_269513

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"."

Paul Newcome

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)

Daniel Hollywood

@Paul Newcome

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!

Paul Newcome

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))