Year function

[Raz]

I need to set up a year counter alongside a set of dates. The year function does this but this gives me calendar year and I need Financial Year. Is there a function which will allow me to get financial year??

 

[Alriemer]

Raz,
Welcome to the board. This may help:

=IF(MONTH($B$6)=12,"","F")&TEXT(DATE(YEAR(B10),MONTH(B10)+(12-MONTH($B$6)),DAY(B10)),"yyyy")

where $B$6 has the date of your fiscal year end (ie, 3/31/01) and B10 has the date of a given period (ie 6/30/01). For example, these two data points should yield F2002.

Alriemer

 

[Dave Patton]

You could use a Lookup formula or a formula.

=IF(MONTH(B10)>3,"F"&YEAR(B10)+1,"F"&YEAR(B10))

or ="F"&YEAR(B10)+LOOKUP(MONTH(B10),{1,4;0,1})

="F"&YEAR(B10)+(MONTH(B10)>3)&"-"&LOOKUP(MONTH(B10),{1,2,3,4,5,6,7,8,9,10,11,12;10,11,12,1,2,3,4,5,6,7,8,9})

or A Lookup table to accommodate special month-end dates
This message was edited by Dave Patton on 2002-10-08 19:03