Calculating Fiscal Year in YY format

Question

I found this post and it worked for a 4-digit year but I am trying to use a 2-digit year

Calculating Fiscal Year

This will give you the format 2013-2014, 2014-2015:

=IF(MONTH(Created)>6,YEAR(Created)&"-"&YEAR(Created)+1,YEAR(Created)-1&"-"&YEAR(Created))

It takes MONTH>6, so you could adjust that parameter for fiscal start.

And for FY 2014, FY 2015 etc, you could use this formula:

=IF(MONTH(Created)>6,"FY "&YEAR(Created)+1,"FY "&YEAR(Created))

that formula returned 2015.

I tried using the formula TEXT([DateField],”yy”) and nesting it inside the datefield area. it returns 05 for (1905) instead of 15 for 2015

Is there a way to return in the YY format?

Answer 1

Try

=IF(MONTH(Date)>6,"FY "&RIGHT(YEAR(Date)+1,2),"FY "&TEXT(Date,"yy"))

Answer 2

Fiscal years start on Oct. 1, at least the government goes off of Oct. 1, so to accommodate that, it ought to be:

=IF(MONTH([Created])>9,RIGHT(YEAR([Created])+1,2),TEXT([Created],"yy")) 

If the month is after September, it will take the year, add one, then take the last 2 characters. Otherwise, we just get the "yy" of the "Created" date field.

Breakdown

The statement takes the form: IF(some condition,do this if met,else do this)

RIGHT(some field, n) will grab as many n characters as you specify starting from the right, backwards, going to the front of the value. (Using LEFT(some field, n) grabs n characters starting on the left, going forwards.)

You can get any mm-dd-yyyy or yy value from a date using TEXT(field,chars). Example: Given a column "Publication Date", you could do this: TEXT([Publication Date],"mm") and get 07 if the date was 07-02-21. You can also just do MONTH([Publication Date]), and that works for numeric comparisons, whereas using the TEXT() operator does not.

Answer 3

Thanks, everyone. I wanted to get the "FY2122" format. Based on the comments from everyone here, I used the following formula, which worked. Might be useful for others too.

=IF(MONTH([DateField])>6,"FY"&TEXT([DateField],"yy")&RIGHT(YEAR([DateField])+1,2),"FY"&RIGHT(YEAR([DateField])-1,2)&TEXT([DateField],"yy"))