+ Reply to Thread
Results 1 to 4 of 4

Counting unique dates in a column

  1. 02-27-2023, 03:49 PM #1
    DCatboone
    DCatboone is offline
    Registered User
    Join Date
    02-14-2023
    Location
    Illinois, USA
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2202 Build 16.0.14931.20858) 64-bit
    Posts
    21

    Counting unique dates in a column

    Everything i found online consisted of a table that only contained one month, and returned the unique dates for that month. The data I'm working with has multiple months in it, and I'm trying to count how many unique dates occurred per month. I tried converting all the dates to their code in a different column, but I'm still unable to figure out how to count the unique dates between two dates (01oct,31oct). I was told, "just use a pivot table", but I am trying to extract this information for a report, and only want to display the number of days worked per month in order to divide the total count of entries per month by the unique dates in order to display the total number of tasks completed per work day.

    currently i'm using the following to estimate the number of work days :
    =IF(MONTH(C$11)=MONTH($B$10),C$9/IF(SUM(DAYS($B$10,C$11),-PRODUCT(DAYS($B$10,C$11),0.4))=0,1,SUM(DAYS($B$10,C$11),-PRODUCT(DAYS($B$10,C$11),0.4))),C9/(SUM(C2,-12)))

    it's a bit inaccurate but it gets pretty close.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-27-2023, 04:02 PM #2
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Counting unique dates in a column

    To solve this easily, use dates instead of strings for the month names in F3:H3. I have provided the formula to count unique dates for each month. See attached. Formulas below.

    I'm not really sure how you are counting tasks. Is each row 1 task? If so you are calculating the average number of tasks per day. Otherwise "tasks per day" does not make sense to me.
    Values as displayed
    E
    F
    G
    H
    3
    Jan
    Feb
    Mar
    4
    Days Worked
    6
    5
    4
    5
    Tasks Per Day
    2
    3
    2

    Underlying formulas
    E
    F
    G
    H
    3
    44927
    =EOMONTH(F3,0)+1
    =EOMONTH(G3,0)+1
    4
    Days Worked
    =COUNT(UNIQUE(FILTER(Table1[Date],(Table1[Date]>=F$3)*(Table1[Date]<=EOMONTH(F$3,0)))))
    =COUNT(UNIQUE(FILTER(Table1[Date],(Table1[Date]>=G$3)*(Table1[Date]<=EOMONTH(G$3,0)))))
    =COUNT(UNIQUE(FILTER(Table1[Date],(Table1[Date]>=H$3)*(Table1[Date]<=EOMONTH(H$3,0)))))
    5
    Tasks Per Day
    =COUNT(FILTER(Table1[Date],(Table1[Date]>=F$3)*(Table1[Date]<=EOMONTH(F$3,0))))/F4
    =COUNT(FILTER(Table1[Date],(Table1[Date]>=G$3)*(Table1[Date]<=EOMONTH(G$3,0))))/G4
    =COUNT(FILTER(Table1[Date],(Table1[Date]>=H$3)*(Table1[Date]<=EOMONTH(H$3,0))))/H4
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]
    Reply With QuoteRegister To Reply
  3. 02-27-2023, 05:22 PM #3
    DCatboone
    DCatboone is offline
    Registered User
    Join Date
    02-14-2023
    Location
    Illinois, USA
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2202 Build 16.0.14931.20858) 64-bit
    Posts
    21

    Re: Counting unique dates in a column

    Thanks! Works perfectly! I didn't know about the filter thing, that is what sorts the dates by month (in this case).
    Reply With QuoteRegister To Reply
  4. 02-27-2023, 09:15 PM #4
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Counting unique dates in a column

    It's a more recent addition to Excel, and since you are using 365 it works (same for UNIQUE).
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Counting Consecutive Dates with a Unique ID
    By DataLuke in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-10-2022, 12:37 AM
  2. [SOLVED] Counting unique dates in table
    By sovietchild in forum Excel General
    Replies: 6
    Last Post: 12-17-2021, 01:19 PM
  3. Counting unique dates with criteria
    By tisko in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-16-2021, 04:41 AM
  4. [SOLVED] Counting Unique dates and names
    By Streaky in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-21-2018, 05:06 AM
  5. [SOLVED] counting unique dates
    By guy13 in forum Excel Formulas & Functions
    Replies: 23
    Last Post: 09-22-2014, 10:50 AM
  6. 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
  7. Need Help counting unique values between two dates
    By Lm1164 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-27-2013, 09:46 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1