Calculating Months/Yr including partial months

Hello - I am trying to calculate the number of months in between two dates for a given year. The formula needs to take into account partial months as the number of days divided by the number of days in that month so 30 if November, 29 if February on a leap year, etc. The attached spread sheet shows for example what I need. I've been working on this for a while and can't get it right.

I started with lookups and countif and have a list of all days since January 2004 in a look-up range, but besides not giving me the exact answer there has to be a simpler way than listing all days in a lookup/reference table. Any help would be greatly appreciated!!

Hi
What happens if the number of months is not an integer ? Roundup or rounddown



EDIT to get the number of whole months between 2 dates, try

Please Login or Register  to view this content.

Is that what you need ?

Are you running exclusively on XL2007 and above or do you need backwards compatibility ?
If the latter - are you able to utilise Analysis ToolPak ? (you can do with/without but "with" will allow for shorter formulae)

I'm shooting off line for a bit but assuming answers to the above are either a) 2007+ or b) pre XL2007 but ATP ok; then:

Please Login or Register  to view this content.

Slightly different approach but should get the same results.....

Assuming you put the year in row 4 as per DonkeyOte's suggestion, then you could use this formula in D7 copied across and down

=SUMPRODUCT(FREQUENCY(ROW(INDIRECT($B7&":"&$C7-1)),DATE(D$4,{1;2;3;4;5;6;7;8;9;10;11;12;13},0))/DAY(DATE(D$4,{1;2;3;4;5;6;7;8;9;10;11;12;13;14},0)),{0;1;1;1;1;1;1;1;1;1;1;1;1;0})

I suppose it depends exactly how you would treat dates like 31st March......but you could try this version with YEARFRAC function.

In D7 copied across and down

=IF(($B7<DATE(D$4+1,1,1))*($C7>DATE(D$4,1,1)),YEARFRAC(MAX($B7,DATE(D$4,1,1)),MIN($C7,DATE(D$4+1,1,1))),0)*12