2 Digit Financial Year

[abschy]

Hi guys,


Im having some difficulty formatting a 2 digit financial year.


I currently have a column for the installation date --> which contains either just a month, or a specific date
and a column for financial year --> this contains this equation:


=IFS([@[Inst''n Completed]]="TBC","TBC",[@[Inst''n Completed]]=""," ",ISNUMBER([@[Inst''n Completed]]),YEAR([@[Inst''n Completed]])+(MONTH([@[Inst''n Completed]])>=7),OR([@[Inst''n Completed]]="January",[@[Inst''n Completed]]="February",[@[Inst''n Completed]]="March",[@[Inst''n Completed]]="April",[@[Inst''n Completed]]="May",[@[Inst''n Completed]]="June",[@[Inst''n Completed]]="July",[@[Inst''n Completed]]="August",[@[Inst''n Completed]]="September",[@[Inst''n Completed]]="October",[@[Inst''n Completed]]="November",[@[Inst''n Completed]]="December"),"TBC")

basically what this equation is:
- if the cell contains "TBC", the financial year will be "TBC"
- if the cell contains any month, the financial year will be "TBC"
- if the cell contains a full date (dd/mm/yy), the financial year is calculated like in the equation

When I try to format the financial year column to display just "19" instead of "2019", it gives me "05" instead.. not sure whats going on but i cant seem to find anything to solve this.. or if anyone knows a way to shorten the above equation, im all ears!




Thank you!

 

[Giordano Bruno]

Sure is baffling. Here in Australia, the financial year starts 1st July. For your problem, I would use the formula =IF(A1="","",IF(ISERROR(DATE(DAY(A1),MONTH(A1),YEAR(A1))),A1,IF(MONTH($A1)>6,YEAR($A1)-2000&"/"&YEAR($A1)-1999,YEAR($A1)-2001&"/"&YEAR($A1)-2000)))

 

[abschy]

Hi, Thanks for your help!
For me, my financial year starts 1st July as well!

Is there any way for the outcome to be the later year?
Currently it gives "18/19", but what I want is just "19" if thats possible?


Thanks!!

 

[steve the fish]

Try this:

=IFS(A1="","",ISTEXT(A1),"TBC",ISNUMBER(A1),TEXT(A1,"yy")+(MONTH(A1)>6)+0)

 

[abschy]

Hi Steve!

That works great!!
Thanks so much!