Financial Years

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
Click to expand...

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"
 
Back
Top