Calculating months between dates

[BrWolv]

Hi all,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I am trying to figure out how to calculate the number of months between 2 dates based on 3 possibilities;<o></o>
If the 1<SUP>st</SUP> date happens before the start of the quarter, I need it to return a zero (no commitment in that quarter)<o></o>
If the 1<SUP>st</SUP> date falls between two dates, I need to calculate the amount of months (to 2 decimal points, i.e., 2.3 months) between the 1<SUP>st</SUP> date and the start of the quarter.<o></o>
Lastly, if the 1<SUP>st</SUP> date happens after the end of the quarter, then I just need to return 3 (the total months in the quarter. All of these values will then be multiplied by my cost per month, resulting in my total financial commitment for that quarter. I have been at this all day & my brain is cooked, if my explanation makes any sense, can someone help me out. I have been close many times, however, I keep running in to trouble when trying to change the start and end dates of the quarter to look at my forecasted commitments. I would really appreciate it!!<o></o>
<o> </o>
Thanks <o></o>

 

[Steve=True]

Assuming your dates Start in A2 and are in the A column.

Quarter Start is I1
Quarter End is J1

Put this formula in B2 and copy down:

=IF(A2<$I$1,0,IF(AND(A2>=$I$1,A2<=$J$1),MONTH(A2)-MONTH($I$1),IF(A2>$J$1,3,"ERROR")))

You didn't mention if a date in the same month as the quarter should = 1 or Zero (subtracting the quart month from the 1st date).

This can be solved by changing the first >=$I$1 to >

Steve=True
www.exceldashboardtemplates.com