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.

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.

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


or with fewer function calls:


=YEAR(A1)-(MONTH(A1)<4)
--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"JE McGimpsey" wrote in message
...
one way:

instead of

=YEAR(A1)

use


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.

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.

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.

Look under toolsoptionstransition if you have transition formula
evaluation checked, of so uncheck it, only other option would be a date
earlier than Apr 1900 or an empty cell


--

Regards,

Peo Sjoblom


"Martin_London" wrote in message
...
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(DATE(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.