View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default year function and financial years

Hi Martin

You seem to have a minus sign in front of SPVs!$F$12 throughout the formula

In some cases there is a number in front of the minus sign, in others there
is not.
I suspect that is what is giving you the problem.

Also, using Sandy's suggestion for calculating the Year

Year(D$3-(Month(D$3)<4) in place of
YEAR(DATE(YEAR(D$3),MONTH(D$3)-3,1))
would shorten the formula somewhat and perhaps make it slightly easier to read.

Regards

Roger Govier


Martin_London wrote:
Thanks JE

My formula now reads

=IF(AND(D$3=SPVs!$F$12,YEAR(DATE(YEAR(D$3),MONTH( D$3)-3,1))=YEAR(DATE(YEAR(-SPVs!$F$12),MONTH(-SPVs!$F$12)-3,1))),$O82/(15-MONTH(SPVs!$F$12)),IF(AND(D$3=SPVs!$F$12,YEAR(DAT E(YEAR(D$3),MONTH(D$3)-3,1))=YEAR(DATE(YEAR(-SPVs!$F$12),MONTH(-SPVs!$F$12)-3,1))),$O82/12,0))

with D$3 and SPVs!$F$12 being two dates but I am getting a #NUM! error.

Any ideas?
Martin

"JE McGimpsey" wrote:


one way:

instead of

=YEAR(A1)

use

=YEAR(DATE(YEAR(A1),MONTH(A1)-3,1))

In article ,
"Martin_London" wrote:


In the UK our financial year runs April to March. Is there any way of using
the =year() function on a date so that it runs on these dates rather than
calender years? Basically I need Jan, Feb and March of next year to be
recognised as the same year as April to December of this year.