Count # of months in date range

**JChandler22** · 07-14-2008, 02:20 PM

Hi,

I have a list of hundreds of dates in a single column. I'm trying to figure out how many months are spanned by the list. The earliest and latest dates are not explicitly singled out or located anywhere specific - they may be placed anywhere in the list.

Until now, I've been using this:

=MONTH(MAX(DateRange))-MONTH(MIN(DateRange))+1

...where "DateRange" is the column of data containing the hundreds of dates. This worked well until recently where there are now dates across multiple years. So, for example, if the earliest date is 12/27/07 and the latest date is 3/31/08, that should be 4 months (Dec, Jan, Feb, Mar), instead of 10 as the formula would churn out (12-3+1).

How can I manipulate the formula to get 4? Thanks in advance-

JChandler

**darkyam** · 07-14-2008, 02:40 PM

=(Year(Max(DateRange))-Year(Min(DateRange)))*12+MONTH(MAX(DateRange))-MONTH(MIN(DateRange))+1

**NBVC** · 07-14-2008, 02:45 PM

Try:

=DATEDIF(MIN(DateRange),MAX(DateRange),"m")+1

**JChandler22** · 07-17-2008, 09:00 AM

Thank you very much, that worked well.

I've since encountered another problem.

If the span of months was from Nov '07 to Jun '08, but there are no Feb dates included anywhere in the long list of dates, what function would count 7 instead of 8?

Thanks again in advance.

**Ron Coderre** · 07-17-2008, 09:12 AM

With
A1:A20 containing various dates (and possibly some blanks)

This ARRAY FORMULA (committed with CTRL+SHIFT+ENTER,
instead of just ENTER) returns the count of unique months:

B1: =COUNT(1/FREQUENCY(IF(A1:A20>0,--TEXT(A1:A20,"yyyymm")),--TEXT(A1:A20,"yyyymm")))

EDITED to include this regular (non-array)
AND RE-EDITED to prevent it from returning: 1 when no values are present:

B1: =COUNT(INDEX(1/FREQUENCY(--TEXT(A1:A20,"yyyymm"),--TEXT(A1:A20,"yyyymm"))/(A1:A20<>""),0))

Is that something you can work with?

**NBVC** · 07-17-2008, 09:14 AM

There's probably a better way.. but here's one way...

Say your list of dates covers A1:A6, create a list of all the months in a year somewhere in the sheet, so list "January" to "December" in say F2:F13.

Now use this formula:

=DATEDIF(MIN($A$2:$A$6),MAX($A$2:$A$6),"m")+1-SUMPRODUCT(--ISNA(MATCH($F$2:INDEX($F$2:$F$13,MATCH(TEXT(MAX($A$2:$A$6),"mmmm"),$F$2:$F$13,0)),TEXT($A$2:$A$6,"mmmm"),0)))

which must be confirmed with CTRL+SHIFT+ENTER not just ENTER... you will see { } brackets appear around the formula.

If you make adjustments to the formula, you must reconfirm with the CSE keys.

Count # of months in date range

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Count # of months in date range

Count # of months in date range

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions