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

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



"James B" <James wrote in message
...
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

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

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

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"