Hey all,
Currently I have alot of historical data that I built a workflow around. I built a batch macro to pull in historical data and append it to one excel file. I get a new file each month. I have three date metrics already (Ex: Month=Jan, Year=2019, Month&Year= Jan 2019). I need to create a fourth metric that is Fiscal Year (ex: FY18, FY19). What kind of formula could I create where it would be ongoing so FY17, FY18, FY19, etc..? Each Fiscal year runs like this FY18= July 2018-June 2019; FY19= July 2019-June 2020. Any help would be greatly appreciated thanks!!
Solved! Go to Solution.
First, I'd turn the Monh&Year into an actual date using DateTime Parse. Once I have the date, I can extract Month number from it using DateTimeFormat formula and use the number to build out a conditional statement. Example attached.
IF ToNumber(DateTimeFormat([DateTime_Out], "%m")) < 7 THEN "FY"+Right(ToString([Year]-1),2)
ELSE "FY"+Right(ToString([Year]),2)
ENDIF
@MichalM Almost the exact same solution!
Great minds think alike @LordNeilLord ?
Thanks guys so much, I forgot to mention the data that I'm using comes over as (Ex: "2019-01-31"). I used the Datetime tool to make sure it was formatted correctly but than when I inputted the formula it gave me this. Any thoughts? I also created Year and Month with these formulas pulled from the original "2019-01-31", Would that be as to why I am getting the error " Formula: invalid type in subtraction operator"?