1

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?

2
  • "it returns 05 for (1905) instead of 15 for 2015" can you be more clear on this?
    – Keerthi
    Jun 2, 2015 at 21:16
  • After removing the FY text, the formula returned 1/2/1905 instead of the 1/2/2015 based off of the date field selected when viewing the column in a list. When I nested the formula in this formula: TEXT([DateField],”yy”) it gave me the 2--digit year as requested but it was the year (05) instead of the expected year (15)
    – Mike G
    Jun 3, 2015 at 13:44

3 Answers 3

1

Try

=IF(MONTH(Date)>6,"FY "&RIGHT(YEAR(Date)+1,2),"FY "&TEXT(Date,"yy"))
2
  • This worked for me. I removed the "FY" and came to the 2-digit year i was looking for. Thank You
    – Mike G
    Jun 3, 2015 at 13:35
  • Fiscal years start on Oct. 1. This formula says that any month that is after June belongs to the next fiscal year, which I believe is incorrect. Maybe some firms go off of June-to-June, but 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 Sept., it will take the year, add one, then take the last 2 characters. Otherwise, we just get the "yy" of the "Created" date field.
    – vapcguy
    Jul 2, 2021 at 17:35
1

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.

0

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"))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.