Question

Fiscal year from date field?

Forms Version 10

Updated May 2, 2019

asked on April 30, 2019

Hi,

I am trying out this formula to get the fiscal year when the form's date is today's date.

=IF(MONTH(TODAY())>7,"FY "&RIGHT(YEAR(TODAY()),2)&RIGHT(YEAR(TODAY())+1,2),"FY "&RIGHT(YEAR(TODAY()),2)-1&RIGHT(YEAR(TODAY()),2))

I entered in the advanced section of the date field but getting a syntax error.

Is this formula doable? Does anyone have a better way?

Thank you!

~Jo Anne

0 0

Answer

SELECTED ANSWER

replied on May 2, 2019

I got an error too, trying to use the Right function on the year. I think the Right function expects a string. So, I was able to get this to work in Forms...

=CONCATENATE("FY ", IF(MONTH(TODAY())<7, YEAR(TODAY())-2001, YEAR(TODAY())-2000), IF(MONTH(TODAY())<7, YEAR(TODAY())-2000, YEAR(TODAY())-1999))

3 0

replied on May 2, 2019

Thanks Keith - it worked!

1 0

Replies

replied on May 1, 2019

Here are some workflow steps I'm using to calculate the correct fiscal year:

1 0

replied on May 2, 2019

thank you for your response but this does not meet my needs.

0 0

replied on May 2, 2019

My formula also assumes that your fiscal year is from June to July. So May 2019, would be Fiscal Year 18-19.

1 0

replied on May 1, 2019

Have you tried the CONCAT function rather than & to concatenate your values? Here is the equivalent formula with CONCAT to see if it works...

=CONCAT("FY ",IF(MONTH(TODAY())>7,CONCAT(RIGHT(YEAR(TODAY()),2),RIGHT(YEAR(TODAY())+1,2)), CONCAT(RIGHT(YEAR(TODAY())-1,2),RIGHT(YEAR(TODAY()),2))))

0 0

replied on May 2, 2019

Thank you Keith. I had to change CONCAT to the full name CONCATENATE.

I am inserting it on a Text Field > Advanced but am still getting a syntax error message.

I then tested a simple =TODAY() to see if date comes in, but it comes in text format.

So I believe the error is due to the format defaulting to text. (i.e. =TODAY() displays '43587') How do I get it to see it in short date format?

0 0

You are not allowed to follow up in this post.

Question

Question

Fiscal year from date field?

Answer

Replies

Sign in to reply to this post.