Excel 2007 : count number of times end of month is passed

[Sazza]

Hi Guys,

I have a formula which count the number of times the last day in the month is past within a date range. But it is not working as i would expect and would appreciate your help ...

I have a date range as follows
F4 = Start date in range
G4 = End Date in range

My buggy code looks like this at the mo :

Please Login or Register  to view this content.

Example of how i need this to count :
F4 = 24/11/2011
G4 = 26/01/2012

And i need this to return a count of 2 ( as 31/11/11 and 31/12/11 are passed within the date range )

Currently my code is returning 3 ??

If the end date in the range is the last day of the month i need to count that too ...

Any idea where im going wrong ?

Thanks,
Saz

[Domski]

Maybe: =DATEDIF(F4,G4,"m")+(G4=EOMONTH(G4,0))

Dom

[Domski]

Actually don't think that's right.

Try: =(MONTH(G4)-MONTH(F4))+((YEAR(G4)-YEAR(F4))*12)+(G4=EOMONTH(G4,0))

Dom

[Sazza]

In the few examples i have just tried this works perfectly thanks a mill

[daddylonglegs]

This formula should also give you the correct result

=DATEDIF(F4-DAY(F4)+1,G4+1,"m")