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!

 

[jasonb75]

What level of accuracy is required on the ratio?

For example, if the date entered was 12/1/2010 what result would you expect? Same for 12/15/2010?

=YEAR(EOMONTH(A1,3))+ROUNDDOWN(MONTH(EOMONTH(A1,3))/12,2)

Is on the right lines, but may, or may not give the desired results.

 

[cornflakegirl]

I'm sure there's a much nicer way but:

=YEAR(DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)))+IF(MONTH(A1)>=10,DATEDIF(DATE(YEAR(A1),10,1),A1,"m")+1,DATEDIF(DATE(YEAR(A1)-1,10,1),A1,"m")+1)/12

jason's point about partial month's very much relevant to my solution too.

 

[Evagrius Ponticus]

Guys - thank you very much for the help - I am getting closer. The level of accuracy I am after is at least 15 day increments.

So, both formulas posted seem to work the same. If the date is 3/1/2011, I get 2011.5 for both results. But this answer implies half of a year has passed, when in truth it should be .4167, or 5 month devided by 12 month. This is because only one day in March has passed and it shouldn't count the entire month.

Does that make sense? Thank you for your help!

 

[cornflakegirl]

How about

=YEAR(DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)))+IF(MONTH(A1)>=10,DATEDIF(DATE(YEAR(A1),9,30),A1,"m"),DATEDIF(DATE(YEAR(A1)-1,9,30),A1,"m"))/12

It'll count a full month from the 30th of a month. Does that work for you?

 

[shg]

How about

=DOLLARFR(YEAR(A1) + (MONTH(A1)>=10) + MOD(A1-"10/1", 365)/365, 12)

That gives values from yyyy.00 to yyyy.12:

       -----A----- ---B---
   1   01 Jan 2011 2011.03
   2   16 Jan 2011 2011.04
   3   01 Feb 2011 2011.04
   4   15 Feb 2011 2011.05
   5   01 Mar 2011 2011.05
   6   16 Mar 2011 2011.05
   7   01 Apr 2011 2011.06
   8   16 Apr 2011 2011.06
   9   01 May 2011 2011.07
  10   16 May 2011 2011.07
  11   01 Jun 2011 2011.08
  12   16 Jun 2011 2011.08
  13   01 Jul 2011 2011.09
  14   16 Jul 2011 2011.09
  15   01 Aug 2011 2011.10
  16   16 Aug 2011 2011.10
  17   01 Sep 2011 2011.11
  18   16 Sep 2011 2011.12
  19   01 Oct 2011 2012.00
  20   16 Oct 2011 2012.00
  21   01 Nov 2011 2012.01
  22   16 Nov 2011 2012.02
  23   01 Dec 2011 2012.02
  24   16 Dec 2011 2012.02

 

[shg]

If you wanted the week number (yyyy.01 to yyyy.52)

=DOLLARFR(YEAR(A1) + (MONTH(A1)>=10) + FLOOR(MOD(A1-"9/30", 365)/365, 1/52), 52) + 0.01

 

[barry houdini]

This would give you an approximation.....

=YEARFRAC(0,A1)+1900.25

 

[cornflakegirl]

=DOLLARFR(YEAR(A1) + (MONTH(A1)>=10) + MOD(A1-"10/1", 365)/365, 12)

Oh, that's nice.

(Although I think the DOLLARFR bit is actually a step further than what EP requested?)

 

[jasonb75]

A couple of options for you

This one does half month increments, rounded up

=YEAR(EOMONTH(A1,3))+(((MONTH(EOMONTH(A1,3))*2)-(DAY(A1)<=(DAY(EOMONTH(A1,0))/2)))/24)

This one works to 1 day increments

=YEAR(EOMONTH(A1,3))+(DATEDIF(--("01/01/"&YEAR(EOMONTH(A1,3))),A1+92,"d")/365)

Does either of these give results as expected / desired.