Finding a way to count number of months per fiscal year

Thank you for your assistance.  I am trying to enter the formulae as suggested but am receiving either #NAME? or REF errors.  But you have given me much more to work with than I had before.  I will keep working on it.  Thank you so much for your suggestions.

You could use the largely undocumented DATEDIF() function, parsing the yyyymm dates and comparing then to the start and end of the fiscal year.

D1:M1 contain the years (e.g. 2003, 2004, etc) with a custom number format of "F"0.

The formula in C2 is,

=DATEDIF(DATE(LEFT(A2,4),RIGHT(A2,2),1),DATE(LEFT(B2,4),RIGHT(B2,2)+1,1),"m")

... which can be filled down. The formula in D2 is,

=IFERROR(DATEDIF(MAX(DATE(D$1,9,1),DATE(LEFT($A2,4),RIGHT($A2,2),1)),MIN(EDATE(DATE(D$1,9,1),12),DATE(LEFT($B2,4),RIGHT($B2,2),1)),"m"),0)

... which can be filled both right and down.

Please note that some unreliable results may be returned with DATEDIF(), particularly with counting days around leap years (which you are not doing). If the number of dates is absolutely mission critical, you should at least know what you are up against. See the following,

The DATEDIF Worksheet Function

Excel - Formula Frustration

I'm not sure to whom you are replying, but here are some general comments regarding your reply.

If you are receiving #NAME? errors, then you may have inadvertently mistyped one of the function names or in the case of EOMONTH() or EDATE(), need to install the Analysis ToolPack. To install it in Excel 2003 click Tools ► Add-Ins, then check the box next to Analysis ToolPack and click OK in the lower right. Note that this will have to be installed on every computer that will use this workbook.

The #REF! errors are very likely bad cell or range references. Double-check each cell or range reference that you modified for your own purposes.

I have a rather lengthy document which has 6 fields. The last 3 fields are start date, end date and total months. The total months could span over more than one fiscal year. I am seeking a way to break down each record to show the number of months for each fiscal year. Any possible solutions would be greatly appreciated.  The fiscal year runs from September through August.  For example, the first record has a start date of 200404 and end date of 200907.  The total number of months is 64. I would like to find a way determine the number of months in FY 2004, 2005, 2006, 2007 & 2008. 

The next record starts with 200508 and ends with 200909 which comes out to 50 months and spans FY 2005 through 2009. Again would like to know how many of those 50 months were in each applicable FY.  There are a few hundred records in this file.

We are expending numerous hours with manual calculations and I am hopeful there might be a formula that could help with this.  Any assistance/possible solution would be most welcome and appreciated. 

Thank you. 

The following result

is obtained by the following formulas:

In cell C2:

=SUM(D2:J2)

In cell D2:

=SUMPRODUCT(--(EOMONTH(DATE(D$1-1,9,1)-1,ROW($1:$12))>=$A2),

                           --(EOMONTH(DATE(D$1-1,9,1)-1,ROW($1:$12))<=EOMONTH($B2,0)))

Copy cell D2 across to cell J2 (or further if you have more years on row 1).

Copy cells C2:J2 down as far as you have data in columns A and B.

Hope this helps / Lars-Åke

Hi,

Doing this for a single year and the formula would be quite compact but a fiscal year of Sep to Aug makes it more complicated. Such is life.

I have assumed the start date and end date are properly formatted dates.

I have assumed your earliest start date is 2004 and the start date and end date are in D1 and E1 respectively. This formula does months in fiscal year Sep 2004 - Aug 2005

=SUMPRODUCT(--(DAY(ROW(INDIRECT($D1&":"&$E1)))=1)*(MONTH(ROW(INDIRECT($D1&":"&$E1)))>=9)*(YEAR(ROW(INDIRECT($D1&":"&$E1)))=2004))+SUMPRODUCT(--(DAY(ROW(INDIRECT($D1&":"&$E1)))=1)*(MONTH(ROW(INDIRECT($D1&":"&$E1)))<=8)*(YEAR(ROW(INDIRECT($D1&":"&$E1)))=2005))

Because you have excel 2003 you don't have enough columns to enable the formula to be dragged right. column BYB is column 2004 and you don't have that so I have had to use numbers for the years underlined. So for 2005 - 2006 you would advance each year manually by 1 but you would only have to do this once then the formula can be dragged down