Extract a 'Reporting Year' from date

[Kellis]

Hi,

Need some expert guidance on the best way forward to extract a Year (Reporting year) from a date. I.e RY 2015 runs from April 1st 2015 to 31st March 2016. I have a list of dates i.e. 02/04/15 which I need to extract a RY 2015 another date 02/04/2014 would be RY 2014. Any suggestions?

Thanks

 

[PaulF]

=YEAR(A2)

 

[Kellis]

Thanks, that is not suitable. The reporting year starts in April and runs till March I need a away of converting, working it out and I cannot think of a way to break it down. I.e If month between April and March in 2014 RY will be 2014, if date between April 2015 and March 2015 RY will be 2015. I just cannot think of the way forward.

 

[PaulF]

I should have read your entire post Sorry... Can you upload a sample... I have an idea...

 

[Kellis]

My file has three rows, yet site says to large to update. Don't figure.

 

[PaulF]

See if this works for you... I took the date year * 100 + month and then did a match against a table with an index to retrieve the desired year...

 

[eibi]

Kellis,

I think that the formula you want is:

=YEAR(EOMONTH(B2,-3))

Where B2 is the target date...

See attached.

 

[PaulF]

=YEAR(EOMONTH(B2,-3))

^^ 10 times easier and better than what I proposed...

 

[Kellis]

Brilliant thanks muchly.

 

[Khalid NGO]

Hi,

Just sharing another simple one, little longer then Isaac:

=IF(MONTH(B2)<=3,YEAR(B2)-1,YEAR(B2))

Regards,