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 '15 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 '15 at 13:44
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 '15 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 at 17:35
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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