Calculate Fiscal Year from a Date

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!

Help

Accepted answers

Olo_DC-OCP

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.

All comments

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!

assign_fiscal_year.yxmc

Olo_DC-OCP

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.

Thavish

Going off your solution, I did this with two consecutive formulas.

-----------

Formula 1:

IF [Month] < ToNumber( [User.Fin_Year_Start] )
THEN [Year] - 1
ELSE [Year]
ENDIF

Formula 2:

IF ToNumber([User.Fin_Year_Start]) != 1
THEN
[Financial_Year] +
"/" +
ToString( ToNumber([Financial_Year]) + 1 ),

ELSE
[Financial_Year]

ENDIF

-----------

In my data, [User.Fin_Year_Start] is a workplace variable I declared, [Financial_Year] is a string, while [Month] and [Year] are numbers.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703