Help with a formula to look back to the previous year

The below formula is intended to update the Edition Date on the end of a long character string known as our Form Number for our different projects. Each piece has it’s own form number which can look something like this: TSF-26-PTSGX-2106.
Now the Edition Date, the last for digits in the form number, needs to read as a two digit Year “21” and two digit Month “06” for projects in Q2. Everything works exactly like it should, as we work on Q2 pieces right at the beginning of Q3 and this formula looks back a month to the previous month to update the Edition Date.

THE ISSUE? When creating the formula, I didn’t take into account that if I say the start work date for Q4 items from the previous year is January the following year, the formula doesn’t know how to look back at the previous month “12” and state “12” instead of stating “00” (01 less than 01 is 00) Any thoughts on how to fix the below formula to say, if the edition date column is marked as 1/1/2021, that the formula should state the Year as 2020 “20” and the Month as “12” ultimately looking like TSF-26-PTSGX-2012.

CONCATENATE({Form # Prefix#Labels}, (FORMAT_DATE({Edition Date},“YY”)), IF((MONTH({Edition Date})-1)=0,“12”,IF(MONTH({Edition Date}) < “11”, (CONCATENATE(“0”,(MONTH({Edition Date})-1))), (MONTH({Edition Date})-1))))

1 Like


Try this:

{Form # Prefix#Labels} & TEXT(MOD((YEAR({Edition Date})-IF(MONTH({Edition Date})=1,1,0)),100),"00") & TEXT(MOD(MONTH({Edition Date})+10,12)+1,"00")

JCorrell, by gum, you nailed it! Thank you! Very much appreciated!

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.