Date Period & Number of Months it Indicates.

[The Exceller]

I am in need of coming up with a formula that will give me an answer as to how many months are represented between two dates written in excel.

I have the dates in two different cells but the number of months that needs to come out of the formula needs to follow certain guidelines. This is based on the fact that some companies Fiscal Year's runs from April to March of each year.

My formula needs to follow that if the 2 dates are:

Starts: 01/15/2010
Ends: 01/15/2011

Then for:

2010 Fiscal year: There are 2.5 months (1/15/2010 - 3/31/2010)
2011 Fiscal year: There are 9.5 months (4/1/2010 - 1/15/2011)

if the start and end date were:

Starts: 01/15/2010
Ends: 06/15/2011

Then for:

2010 Fiscal year: There are 2.5 months (1/15/2010 - 3/31/2010)
2011 Fiscal year: There are 12.0 months (4/01/2010 - 3/31/2011)
2012 Fiscal year: There are 2.5 months (4/1/2011 - 6/15/2011)


I have left these detailed instructions in the attached.
Please let me know if you have questions and I can answer.

If you can figure out the formula, you are a genius and I very very much appreciate it!

Thanks you!

[NBVC]

I am not 100% sure I understand all the criteria...

Like what if year is greater than that in G12...

but try this and let me know what's wrong about it, if anything:

=IF(RIGHT(E22,4)+0<YEAR($G$12),12,IF(MONTH($G$12)>=4,12,($G$12-DATE(YEAR($G$12)-1,4,1))/30.3333))

[shg]

Please Login or Register  to view this content.

The formula in C2 and across is

=MAX(0, (MIN(DATE(C1,3,31), End) - MAX(DATE(C1-1,4,1), Start) ) * 12/365)

or

=MAX(0, (MIN("3/31/"&C1, End) - MAX("4/1/"&C1-1, Start) ) * 12/365)

[The Exceller]

NBVC,

Your formula works except for the fact that it won't give the right number of months if the year is the same as the start year but the month is greater than 04 (April).

Shg,

Your formula seems to work just fine when I put it in horizontal format like you have shown above, but it does not work when I type it into the columns the rows that I need them to be in starting in cell E22 down to E26.

Here Is what I've got typed in cell E22:

=ROUND(MAX(0,(MIN("3/31/"&RIGHT(E22,4)+0,$G$12) - MAX("3/31/"&RIGHT(E22,4)+0-1,$G$11))*12/365),2)

which you should then be able to drag down the list and get the number of months for all years. The problem is that it shows 12.0 or 12.3 when
the start date is: 01/15/2010 and
the end date is: 02/01/2013 for all years all the way through 2015 and further.

It should be reading:
Start End 2010 2011 2012 2013 2014 2015
1/15/2010 2/1/2013 2.47 12.00 12.03 10.09 0.00 0.00

Which for some reason works when I have your formula in the horizontal format like I have just shown.

Any Ideas for either of you? NBVC or Shg?

Thanks a lot.

[The Exceller]

Let me make that table more clear. It didn't show up visible.

"It shoud be reading:"
Start End 2010 2011 2012 2013 2014 2015
1/15/2010 2/1/2013 2.47 12.00 12.00 10.09 0.00 0.00

[shg]

First, delete the leading apostrophe in the begin and end dates, so they're actually dates.

Second, change E21 and down to simple numbers: 2010, 2011, ...

Then in G21 and down, =MAX(0, (MIN("3/31/"&E21, $G$12) - MAX("4/1/"&E21-1, $G$11) + 1) * 12/365)

[The Exceller]

If I take out the apostrophe in the begin and end dates the formula for this works correctly but it messes up another set of formulas I have in another part of my spreadsheet. Is there any way to do this with keeping the apostrophe's in? Is there another solution/formula?

[NBVC]

NBVC,

Your formula works except for the fact that it won't give the right number of months if the year is the same as the start year but the month is greater than 04 (April).

...

Any Ideas for either of you? NBVC or Shg?

Thanks a lot.

Your criteria in the yellow box doesn't really mention the date in G11 (the start date)... except for in an example.. which doesn't correspond to the criteria mentioned... i..e. all your criteria refer to the G12 end date...

[The Exceller]

NBVC,
You're right about all the criteria refering to G12, the end date. That's because there's not need to worry about the begin date given that I'm searching for the No. of months beyond 4/1 of the begin date year. You'll see that cell G21 has the calculation for the No. of months from the begin date to 4/1 of the begin date year. Any date after that should coincide with Fiscal Year 2011 and therefore is placed in cell G22 where the formula I need should begin. That's why I only refered to the end date G12 in the comment box.

[shg]

Is there any way to do this with keeping the apostrophe's in?

Yes, but it perpetuates a very poor design:

=MAX(0, (MIN("3/31/"&E21, $G$12+0) - MAX("4/1/"&E21-1, $G$11+0) + 1) * 12/365)

[The Exceller]

Shg,

This one works perfectly! Thanks for your help!