+ Reply to Thread
Results 1 to 6 of 6

Count number of unique values which are a given day of the week in a list of dates.

  1. #1
    Registered User
    Join Date
    04-20-2004
    Posts
    9

    Count number of unique values which are a given day of the week in a list of dates.

    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.

  2. #2
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Count number of unique values which are a given day of the week in a list of dates.

    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?

  3. #3
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Count number of unique values which are a given day of the week in a list of dates.

    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 *

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Count number of unique values which are a given day of the week in a list of dates.

    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).
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Registered User
    Join Date
    04-20-2004
    Posts
    9

    Re: Count number of unique values which are a given day of the week in a list of dates.

    Many thanks!

  6. #6
    Registered User
    Join Date
    03-27-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    18

    Re: Count number of unique values which are a given day of the week in a list of dates.

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Diaply week number from list of dates
    By john dalton in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-19-2013, 04:44 PM
  2. Counting number of unique dates per month in a list of duplicate dates
    By Rackle83 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-22-2013, 10:15 AM
  3. Count Number of Unique Dates For Employee Name
    By Eddiegnz1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2012, 12:20 PM
  4. List of Dates- Assign Week Number??
    By Micquia in forum Excel General
    Replies: 1
    Last Post: 07-11-2006, 05:05 PM
  5. need to convert list of dates to count no. of dates by week
    By neowok in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 01-30-2006, 11:54 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1