Loretta
No matter how you look at it a fiscal year has 12 months is it. It just varies
in month that the year changes. IE Calendar year always starts in Jan and ends
in Dec. A Fiscal year could start in Jul and end in June
Now if you have a project start date and end date, count months, divide by 12
and you will have number of "years" that the project takes
: Loretta wrote....
: > Kevin, I'm familiar with this function and have used it
: > to get the total number of months between two dates. But
: > this does not tell me how many months are in each year.
: > Any suggestions?
: >
: > Thanks!
: > Loretta
: >
: >
: >>-----Original Message-----
: >>Loretta...
: >>> Can anyone help me with a formula that will calculate
: > the
: >>> number of months in a fiscal year (not a calendar year)?
: >>>
: >>> Facts: Fiscal Year begins on October 1st.
: >>>
: >>> I have start dates and end dates for multiple phases of
: > a
: >>> project and I need to determine how many months are in
: >>> each fiscal year.
: >>>
: >>> Example:
: >>>
: >>> Phase 1 Starts 8/1/2004 and ends 11/30/2007
: >>>
: >>> I need to know there are 2004-2 months, 2005-12 months,
: >>> 2006-12 months, and 2007-2 months
: >>
: >>
: >>Loretta,
: >>
: >>The original answer is posted here:
: >
http://tinyurl.com/5jwnp
: >>
: >>I will just copy the answer to save you the effort.
: >>
: >>Hope that helps.
: >>
: >>Kevin
: >>
: >>~~~~~~~~~
: >>
: >>
: >>Check out the DateDif worksheet function. This is
: > undocumented until
: >>Excel 2000(?), so you might not find it in help.
: >>
: >>If A1 is the first payment date, and B1 is the end date,
: > then use:
: >>=DateDif(A1,B1,"M") + 1
: >>
: >>The "M" specifies that you want to know the months
: > difference (you
: >>could use "d" for days or "Y" for years)
: >>
: >>HTH,
: >>Dave.
: >>
:
: Loretta,
:
: Let's first review your original example.
:
: >>> Example:
: >>>
: >>> Phase 1 Starts 8/1/2004 and ends 11/30/2007
: >>>
: >>> I need to know there are 2004-2 months, 2005-12 months,
: >>> 2006-12 months, and 2007-2 months
:
: 8 Jan 2004 through to 30 Nov 2007?
:
: There are 47 months in total? I see from your answer that there is only 28
: months. I think something is amiss.
:
: Here's how I did it. I am assuming your fiscal 2003 year begins on Oct 1,
: 2003.
:
: A1=LastCalYear
: B1=1.10.2003 (1Oct2003)
:
: A2=StartDate
: B2=08.01.2004
:
: A3=EndDate
: B3=30.11.2007
:
: A6=Total Months
: B6=DATEDIF(StartDate,EndDate,"M")+1
:
: A7=2003 Months
: B7=12-DATEDIF(LastCalYear,StartDate,"M")
:
: A8=2004 Months
: B8=MIN(B$6-SUM(B$7:B7),12) - you can copy down
:
: A9=2005 Months
: B9=MIN(B$6-SUM(B$7:B8),12)
:
: A10=2006 Months
: B10=MIN(B$6-SUM(B$7:B9),12)
:
: A11=2007 Months
: B11==MIN(B$6-SUM(B$7:B10),12)
:
: Total Months = 47
: 2003 Months = 9
: 2004 Months = 12
: 2005 Months = 12
: 2006 Months = 12
: 2007 Months = 2
:
: I hope that helps.
:
: Kevin
:
:
:
:
:
:
