6 Replies Latest reply on Mar 26, 2020 10:56 AM by kevin flerlage

# HELP: How to convert fiscal date (calculated field required)

Hi anyone knows how do I convert fiscal name with format like that?

As example, I want to convert below: (Note: for periods with 10, 11, 12 will be as is, only 1-9 will add 0 after "P")

• "FY2019-P1"  into "FY19-P01"
• "FY2020-P9" into "FY20-P09"
• "FY2020-P10" into "FY20-P10"

SO BASICALLY ALL P1-P9 SHALL BE P01-P09 THEN P10, P11, P12 will be as is.

then FY + "last two digits of fiscal year"

• ###### 1. Re: HELP: How to convert fiscal date (calculated field required)

Hi Frank,

Does the screenshot below cover what you need?

Hope that helps

Best regards

Nik

2 of 2 people found this helpful
• ###### 2. Re: HELP: How to convert fiscal date (calculated field required)

Frank,

You'll just have to do some string calculations (using LEFT and RIGHT) to pull the pieces you want.  I opted to do it in 3 calculations for the sake of clarity.

First to get the proper information before the dash:

@Year

'FY' + RIGHT(LEFT([Fiscal], 6), 2) + '-'

This calculation take the left 6 characters (FY2019) then trims off the right two (19), then adds in FY = FY19.

@Month

IF LEN([Fiscal]) = 10 THEN RIGHT([Fiscal], 3)

ELSE 'P0' + RIGHT([Fiscal], 1)

END

This calculation looks at the total length of the value.  If it is 10, then that means it is P10, P11, or P12 and we will just take those 3 characters from the entire string.  If it is less than that, we will take just the right single character and add P0 to it.  The result is P01, P02...P10, P11, etc.

Then we will combine them:

@Combination

[@Year] + [@Month]

And that should do it!

Attached is a workbook containing the calcs.  Let me know if this works for you.

Kevin Flerlage

1 of 1 people found this helpful
• ###### 3. Re: HELP: How to convert fiscal date (calculated field required)

Thank you for your input, sir. I appreciate your help.

• ###### 4. Re: HELP: How to convert fiscal date (calculated field required)

Thank you for your detailed explanation of the solution. You are awesome.

• ###### 5. Re: HELP: How to convert fiscal date (calculated field required)

It's a pleasure, happy to help - and such a great explanation from Kevin!

Main thing is you've got what you needed

Good luck!

Nik

• ###### 6. Re: HELP: How to convert fiscal date (calculated field required)

Yeah, but using MID makes much more sense!  Nice work, Nik.