referencing dates

[PK]

I have a number of dated enteries in a spreadsheet. As our Financial year
starts April 1st and ends March 31st following year I would like to reference
the enteries by financial year.

Fianancial I have dates in column A and would like to reference them by
financial year in column B

A B
21/04/06 06/07
17/10/05 05/06
13/08/06 06/07
17/03/06 05/06

Is this possible

 

[Gary''s Student]

If the date is in A1, then:

=IF(A1<38808,"05/06","06/07")

This will work for all dates in fiscal years 2005 and 2007. Clearly it will
NOT work outside of that.

 

[Billy Liddel]

Sample TAble

Date Year Start Values Sumary Totals
21/04/2006 2006 12.00 2005 46
17/10/2005 2005 34.00 2006 45
13/08/2006 2006 45.00
17/03/2006 2005 12.00

Formula to extract year start in cell B3:
=IF(AND(A3>=DATE(YEAR(A3),4,1),A3<=DATE(YEAR(A3)+1,3,31)),YEAR(A3),YEAR(A3)-1)

Formula in Cell F3
=SUMPRODUCT(--(B3:B6=E3)*(C3:C6))

Years entered as text in column E

Will this help?
Peter

 

[Billy Liddel]

Sorry I forgot to make absolute the ranges for F3. Should have been

=SUMPRODUCT(--($B$3:$B$6=E3)*($C$3:$C$6))
Which makes the values correct.

Peter

 

[David Biddulph]

There's probably a simpler way, but try:
=IF(MONTH(A1)<4,TEXT(MOD(YEAR(A1)-1,100),"00")&"/"&TEXT(A1,"yy"),TEXT(A1,"yy")&"/"&TEXT(MOD(YEAR(A1)+1,100),"00"))