Need Help With A Formula . .

[Evagrius Ponticus]

Hello,

My Fiscal Year begins in October. In a cell A1 I ask the user for the date of the purchase of an Asset. Lets say the user enters 11/30/2010.

In anther cell A2 I ask the user for the FISCAL YEAR purchase of the asset as well as the ratio of months that have passed in the fiscal year over the the full year.

So, for example, in the above sample, the fiscal year would be 2011. Therefore in cell A2 the user would enter 2011.16. The .16 represents 2 months devided by 12 month, or 2/12 since two month have passed in the fiscal year.

I am now trying to create a formula for cell A2 - but can't seem to get it quite right. Thank you for any help!

 

[shg]

=YEARFRAC(0,A1)+1900.25

That changes fiscal year on 9/30, DLL.

 

[barry houdini]

OK, this one will give the same result as jason's last suggestion....

=YEAR(A1+92)+(A1-DATE(YEAR(A1+92)-1,10,1))/365

although it doesn't take leap years into account........

 

[shg]

I think the formula for week number should be corrected to

=DOLLARFR(YEAR(A1) + (MONTH(A1)>=10) + INT((A1-"10/1")/7) / 52, 52) + 0.01

 

[jasonb75]

OK, this one will give the same result as jason's last suggestion....

=YEAR(A1+92)+(A1-DATE(YEAR(A1+92)-1,10,1))/365

although it doesn't take leap years into account........

Just one point there, both solutions need accuracy to 0.000 to avoid 9/30 being included in the wrong year.

 

[shg]

I'll just continue to flail to get my week formula correct:

=DOLLARFR(YEAR(A2) + (MONTH(A2)>=10) + INT(MOD(A2-"10/1", 365 + (DAY(DATE(YEAR(A2), 2, 29))=29) )/7) / 53, 53) + 0.01

... which I believe works for leap years

 

[Evagrius Ponticus]

Thank you everyone for your amazing help - you guys are stars!.


Jason - your formula works great as well -but as you mentioned, when I enter 9/30/2010, I get 2010.99726. I am working my way through the formula, since it generates perfect results any other time, but what is the .99726? Thank You!!

 

[Evagrius Ponticus]

Shg - this is brilliant! But will you please share why you added the 1900.25? It works great for the fiscal year!!

YEARFRAC(0,A1)+1900.25

 

[shg]

That was Barry's formula ...

 

[Evagrius Ponticus]

Sorry Shg

Would you explain Barry?

 

[jasonb75]

Thank you everyone for your amazing help - you guys are stars!.


Jason - your formula works great as well -but as you mentioned, when I enter 9/30/2010, I get 2010.99726. I am working my way through the formula, since it generates perfect results any other time, but what is the .99726? Thank You!!

What is expected for 9/30/2010?