Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
year function and financial years
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
year function and financial years
=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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
year function and financial years
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
year function and financial years
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Financial Functions | Excel Worksheet Functions | |||
Is there a function for a 'year to date' sum | Excel Worksheet Functions | |||
Question about financial calculation PMT | Excel Worksheet Functions | |||
Excel financial function: PMT | Excel Worksheet Functions | |||
#VALUE in cell but pop up function box show right number | Excel Discussion (Misc queries) |