Given a list of dates, where some of the dates are repeated, how could I determine how many distinct Wednesdays (or any other day) are included in those dates?
Thanks.
Given a list of dates, where some of the dates are repeated, how could I determine how many distinct Wednesdays (or any other day) are included in those dates?
Thanks.
There are some possible methods, but it's almost impossible to know without seeing how the data is organized. Can you upload a sanitized sample file?
Try this...
=SUMPRODUCT((WEEKDAY(A2:A60)=4)/COUNTIF(A2:A60,A2:A60&""))
Where your data is in Range A2:A60. Change highlighted value for other weekdays
Life's a spreadsheet, Excel!
Say thanks, Click *
Hi.
Assuming that your dates are Excel-recognized dates (and not e.g. text) and that they lie in A1:A100, and also that you put your day of choice, using its full English naming, in B1, this array formula**:
=SUM(IF(FREQUENCY(IF(TEXT(A1:A100,"dddd")=B1,A1:A100),A1:A100),1))
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Many thanks!
Hello ACE_XL,
I am having difficulty in understanding a very similar formula. Kindly use the link below:
http://www.excelforum.com/excel-form...om-a-list.html
Please help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks