year function and financial years

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 <A26FC0D3-CAFB-4B08-86C4-3BAEC15153A5@microsoft.com>,
"Martin_London" <Martin_London@discussions.microsoft.com> 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
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk


"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-4CEF28.14125005122005@msnews.microsoft.com...
> one way:
>
> instead of
>
> =YEAR(A1)
>
> use
>
>
> In article <A26FC0D3-CAFB-4B08-86C4-3BAEC15153A5@microsoft.com>,
> "Martin_London" <Martin_London@discussions.microsoft.com> 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(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 <A26FC0D3-CAFB-4B08-86C4-3BAEC15153A5@microsoft.com>,
> "Martin_London" <Martin_London@discussions.microsoft.com> 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(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 <A26FC0D3-CAFB-4B08-86C4-3BAEC15153A5@microsoft.com>,
>> "Martin_London" <Martin_London@discussions.microsoft.com> 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 tools>options>transition 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" <MartinLondon@discussions.microsoft.com> wrote in message
news:73BBFA97-4AF1-44E8-854D-DAFFD3D2CB98@microsoft.com...
> 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 <A26FC0D3-CAFB-4B08-86C4-3BAEC15153A5@microsoft.com>,
> > "Martin_London" <Martin_London@discussions.microsoft.com> 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.
> >