Financial Years

[James B]

I have a list of dates and would like to be able to tell which financial year
(1/07 – 30/6) each particular date fell into. I need something to
automatically work out the Fin Year column in the example below.

eg:
Date Fin Year
1/4/05 04/05
31/6/05 04/05
1/7/05 05/06

Thanks in advance

 

[Mangesh Yadav]

With your date in cell A1, try the following:

=IF(MONTH(A1)<7,RIGHT(YEAR(A1)-1,2)&"/"&RIGHT(YEAR(A1),2),RIGHT(YEAR(A1),2)&
"/"&RIGHT(YEAR(A1)+1,2))


Mangesh

 

[Ron Rosenfeld]

On Tue, 16 Aug 2005 20:29:08 -0700, "James B" <James

I have a list of dates and would like to be able to tell which financial year
(1/07 – 30/6) each particular date fell into. I need something to
automatically work out the Fin Year column in the example below.

eg:
Date Fin Year
1/4/05 04/05
31/6/05 04/05
1/7/05 05/06

Thanks in advance

I think this will do it:

=IF(MONTH(A1)>=7,TEXT(A1,"yy\/")&TEXT(
DATE(YEAR(A1)+1,1,1),"yy"),TEXT(DATE(
YEAR(A1)-1,1,1),"yy\/")&TEXT(A1,"yy"))

Except for 31/6/05 which, so far as I know, is a non-existent date


--ron

 

[Jim]

The string
=IF(MONTH(A1)<7,IF((YEAR(A1))=2000,"19",LEFT(YEAR(A1),2))&RIGHT(YEAR(A1)-1,2)&"-"&RIGHT(YEAR(A1),2),LEFT(YEAR(A1),2)&RIGHT(YEAR(A1),2)&"-"&RIGHT(YEAR(A1)+1,2)) return the result in the form "2006-07"