Hi all,
I am looking to populate a cell with a value based upon the number of different months in a column of data containing dates.
Example:
6-Sep-12
14-Sep-12
25-Sep-12
3-Oct-12
19-Oct-12
6-Nov-12
8-Nov-12
26-Nov-12
The above list contains various dates representing three different months. Therefore the answer to the formula I require based on that range of data would be 3.
Many thanks
Could your requirement be reworded as
Find the Earliest Date and the Latest Date.
And you want the number of months between those dates?
Or would there ever be a chance that you have dates in September and November, but none in October, therefore it would only be a count of 2 ?
Hi,
Assuming the data you give above is in A2:A9, this array (important!) formula will give you your desired result:
=SUM(1/(MMULT((TRANSPOSE(ROW(A2:A9)^0)),--(MONTH(A2:A9)=TRANSPOSE(MONTH(A2:A9))))))
Regards
Jonmo1,Originally Posted by Jonmo1
Many thanks for the quick response.
Your last statement is correct. There could be months missing from the data.
Regards,
Then go with XOR LX's solution.
You have to enter it with CTRL + SHIFT + ENTER
XOR LX,
Works perfectly. However, if I extend the range to compensate for yet to be populated cells it returns a greater value as it seeing blank cells as another month.
To explain:
If the current data (containing sept, oct, nov) is in A2:A9 the result is 3. If I extend the range on the formula to A100 to allow for future data entry. The formula returns 4.
Thanks
Try this formula, it can tolerate blanks in the range and dates in different years
=SUM(IF(FREQUENCY(A2:A100,A2:A100-DAY(A2:A100)),1))
confirmed with CTRL+SHIFT+ENTER
Audere est facere
daddylonglegs,
just the ticket. Thank you very much.
Many thanks to all who responded to my post. As a first-time poster this was a great experience.
Hopefully, I can help you all some time.
"...and dates in different years"
Are you sure?
Regards
yep, just checked it by adding dates from 4 different years and the count increased by 1 each time I add a date
Regards,
p.s. who marks posts SOLVED? Is it myself (how?) or moderators?
Yep!
What I mean is that it will count 3-Sep-2012 and 22-Sep-2012 as the same (because they are both September 2012) but it will count 4-Sep-2013 and 15-Sep-2012 as different (i.e. count twice) because the two dates are in different Septembers
"Works perfectly. However, if I extend the range to compensate for yet to be populated cells it returns a greater value as it seeing blank cells as another month."
Why don't you simply make the range dynamic then, so that it adjusts to match the number of populated cells in the range at any given time? So, for example, go to Name Manager and define a new name, Range say, and in the Refers to: box enter:
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(7^7,Sheet1!$A:$A))
Then my formula becomes:
=SUM(1/(MMULT((TRANSPOSE(ROW(Range)^0)),--(MONTH(Range)=TRANSPOSE(MONTH(Range))))))
Regards
Then apologies to both of you.
I read this: "I am looking to populate a cell with a value based upon the number of different months" as meaning months in different years were to be considered the same.
Which means of course that my solution is incorrect.
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks