Date/Month format

[kaurM]

Hello folks,

For my current use case, I need to get the value of previous month in the format MMM-FYY(Feb-F23) ,

• where F23 is Fiscal year 2023, we are now in Fiscal 24 which started from April,2023 to March,2024
• Also there are 4 quarters every fiscal year as Q1 (Apr - June), Q2 (July - Sep), Q3(Oct - Dec), Q4 (Jan-Mar)
• if the previous month is the last month of the quarter, then the output format should be Q4-Xyz-F24 (previous quarter-constant xyz-Fiscal year of the previous quarter)
• To be more clear on what is the expected output, If my bot is running in September 2023, since it’s fiscal year 2024 and current quarter is Q2, the expected output of previous 3 months should be -
  for Aug (Aug-F24), for July (July-F24), for June (Q1-Xyz-F24)

I would appreciate every bit of help here.

Thanks a lot

[Anil_G (Anil Gorthi)]

@kaurM

Please try this

For quarter
If({6,9,12,3}.Contains(Now.Addmonths(-1).Month),If(Now.AddMonths(-1).Month=6,"Q1", If(Now.AddMonths(-1).Month=9,"Q2", If(Now.AddMonths(-1).Month=12,"Q3","Q4"))) + "-Constant-FY",Now.Addmonths(-1).ToString("MMM-")) + If({1,2,3}.Contains(Now.AddMonths(-1).Month),Now.addMonths(-1),Now.addmonths(-1).Addyears(1)).ToString("yy")

Hope this helps

Cheers

[kaurM]

[Anil_G (Anil Gorthi)]

@kaurM

I have modified the formula above can you please check

Cheers

[kaurM]

@Anil_G

If({6,9,12,3}.Contains(Now.Addmonths(-1).Month),If(Now.AddMonths(-1).Month=6,“Q1”, If(Now.AddMonths(-1).Month=9,“Q2”, If(Now.AddMonths(-1).Month=12,“Q3”,“Q4”))) + “-Constant-FY”,Now.Addmonths(-1).ToString(“MMM-”)) + If({1,2,3}.Contains(Now.AddMonths(-1).Month),Now.addMonths(-1),Now.addmonths(-1).Addyears(1)).ToString(“yy”)

Hey, thanks so much for your effort! Will let you know the outcome shortly. Appreciate your help

[kaurM]

Worked like charm! Thanks a ton