Change Date to Financial Year

[RazzPrince]

Hi,

Help Please.

Trying to change the date into financial year Apr-Mar. I'm close and using the below formula;

MID(YEAR(DATE(YEAR(G5),MONTH(G5)+(10-1),1)),3,2)-1&"/"&RIGHT(YEAR(DATE(YEAR(G5),MONTH(G5)+(10-1),1)),2)

with the date in G5 in format xx/xx/xxxx.


The problem i have is dates for 2000 to 2010.

Please help

 

[Joe4]

Can you show us a value that isn't working?
Specifically, an entry in cell G5, then let us know what your formula returns for that date, and what you actually want it to return.

 

[RazzPrince]

Hi, thanks it may have helped if i let everyone know what format i needed.

I wanted to change for example 01/03/1999 to 98/99 as March falls into the 98/99 financial year. The formula i used however struggled because of the -1 when looking at year 2000’s.

I have solved my own question so hopefully might benefit someone else:


01/03/1999 in cell a1. Formula:

=if(month(a1)<=3,year(edate(a1),-12)&”/“&year(a1),year(a1)&”/“&year(edate(a1),+12)

Give 1998/1999

Then can nest the =right(),2 formula to pull into xx/xx format