Date analysis

[EvansB2]

Hi there,

I have long list of Dates as shown in column A below. In column B I want to return the Financial Period to which the date relates. The Financial Period is April to March each year, therefore, I have manually input the correct results in cells B2 & B3. Can a formula be written to speed this up based on this criteria?

Any help would be very welcome.

Ben

[TABLE="width: 155"]
<colgroup><col width="29" style="width: 22pt; mso-width-source: userset; mso-width-alt: 1060;"> <col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3437;"> <tbody>[TR]
[TD="width: 29, bgcolor: transparent"] [/TD]
[TD="width: 82, bgcolor: #D9D9D9"]A[/TD]
[TD="width: 94, bgcolor: #D9D9D9"]B[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9D9D9"]1[/TD]
[TD="width: 82, bgcolor: #B4C6E7"]Date[/TD]
[TD="width: 94, bgcolor: #B4C6E7"]Period[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9D9D9"]2[/TD]
[TD="bgcolor: transparent"]03/02/2010[/TD]
[TD="bgcolor: transparent"]2009-10[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9D9D9"]3[/TD]
[TD="bgcolor: transparent"]04/08/2010[/TD]
[TD="bgcolor: transparent"]2010-11[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9D9D9"]4[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #D9D9D9"]5[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #D9D9D9"]6[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #D9D9D9"]7[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[RoryA]

For a date in A2, you might use:

=YEAR(A2)-(MONTH(A2)<4)&"-"&YEAR(A2)+(MONTH(A2)>3)

 

[EvansB2]

Apologies for the late reply, I've just realised that I need to update my email address after nearly 10 years with the original one!

I will give it a go!

Ben