# Need Help With A Formula . .

#### Evagrius Ponticus

##### Well-known Member
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!

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### jasonb75

##### Well-known Member
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.

Last edited:

#### cornflakegirl

##### Well-known Member
I'm sure there's a much nicer way but:

Code:
``=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

##### Well-known Member
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

##### Well-known Member

Code:
``=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

##### MrExcel MVP

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

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

Code:
``````       -----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``````

Last edited:

#### shg

##### MrExcel MVP
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

##### MrExcel MVP
This would give you an approximation.....

=YEARFRAC(0,A1)+1900.25

#### cornflakegirl

##### Well-known Member
=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

##### Well-known Member
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.

Replies
1
Views
323
Replies
0
Views
395
Replies
0
Views
267
Replies
5
Views
347
Replies
18
Views
3K

1,190,677
Messages
5,982,214
Members
439,769
Latest member
trungminh2802

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back