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

**Sazza** · 02-24-2012, 07:19 AM

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** · 02-24-2012, 07:42 AM

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

Dom

**Domski** · 02-24-2012, 07:55 AM

Actually don't think that's right.

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

Dom

**Sazza** · 02-24-2012, 08:07 AM

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

**daddylonglegs** · 02-24-2012, 08:12 AM

This formula should also give you the correct result

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

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

LinkBack

Thread Tools

Rate This Thread

Display

count number of times end of month is passed

Re: count number of times end of month is passed

Re: count number of times end of month is passed

Re: count number of times end of month is passed

Re: count number of times end of month is passed

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions