Calculate Fiscal Year from a Date

[Olo_DC-OCP]

I have what I hope to be a simple question. I have a column of data that contains dates and I want to create a second columns that indicates the fiscal year in which the date resides. For example, assuming my fiscal year starts on Oct 1, a date of Nov 25, 2015 should equal Fiscal Year 2016 (FY2016). Anyone know how to do this conversion? Many thanks! 

[AmeliaG]

Thanks for your question! I've assigned a macro I created which assigns fiscal year - you'll need to edit the underlying logic to match your specific fiscal year, but it is a great reference for the logic. 

Hope this helps!

[Olo_DC-OCP · Accepted solution]

I think I found an easier way, via formula:

DateTimeYear(DateTimeAdd([Date Field],3,"months"))

[Date Field] is the field containing dates. Adding 3 months should work for fiscal years starting in October; add 2 months for fiscal years starting in November, 4 months for September, etc.

[kymrundel]

Hey! I'm a little late to this party, but I'd love to leverage your macro.

Could you take a screen shot of the format for your fiscal year table that you put into an excel sheet? It doesn't get carried over when I download the macro.

Thanks!

---

@AmeliaG wrote:

Thanks for your question! I've assigned a macro I created which assigns fiscal year - you'll need to edit the underlying logic to match your specific fiscal year, but it is a great reference for the logic. 

Hope this helps!

---

[mottamarco]

A much more elegant solution is to use a formula tool and include the following 2 formulas consecutively:

Create a column called "Financial Year"

1. IF DateTimeMonth([Date])>6 THEN DateTimeYear([Date])+1 ELSE DateTimeYear([Date]) ENDIF

2.IF [Financial Year]="2009" THEN "2008/2009" ELSEIF [Financial Year]="2010" THEN "2009/2010" ELSEIF [Financial Year]="2011" THEN "2010/2011" ELSEIF [Financial Year]="2012" THEN "2011/2012" ELSEIF [Financial Year]="2013" THEN "2012/2013" ELSEIF [Financial Year]="2014" THEN "2013/2014" ELSEIF [Financial Year]="2015" THEN "2014/2015" ELSEIF [Financial Year]="2016" THEN "2015/2016" ELSEIF [Financial Year]="2017" THEN "2016/2017" ELSEIF [Financial Year]="2018" THEN "2017/2018" ELSE "ERROR" ENDIF

You can adjust the number after the > sign if your financial year doesn't finish in June.

I am sure there is a better solution for formula 2 (post it if you have one please), but formula 1 already gives you the fiscal year.

[RonalMarroquin]

Query, where do you document that adding 3 and 2 is the beginning to determine the fiscal year?

[lancejodoin]

Use the following code to translate a Date into a Fiscal Year 

Note: the "- 4" is used within the following SQL because our fical year starts in April which is the fourth month in the year.

select DECODE (SIGN (TO_NUMBER (TO_CHAR (MyTable.MyDate, 'mm')) - 4),
-1, TO_NUMBER (TO_CHAR (MyTable.MyDate, 'YYYY')) - 1,
TO_NUMBER (TO_CHAR (MyTable.MyDate, 'YYYY'))) as MyFiscal

From MyTable;

[nabiakter]

DateTimeYear(DateTimeAdd([Date Field],3,"months")) 

this query only returning the fiscal year in string format. how can I convert it to a datetime format fiscal date column?