+ Reply to Thread
Results 1 to 19 of 19

How do I determine Frequency of occurrences per each Calendar Date?

  1. #1
    Registered User
    Join Date
    07-23-2019
    Location
    San Francisco, CA
    MS-Off Ver
    2013
    Posts
    9

    How do I determine Frequency of occurrences per each Calendar Date?

    I have a spreadsheet that has all the number of times I closed a ticket in any given day. Each row has a closed ticket.

    The column headers are TICKET and DATE CLOSED

    I want to sort that whole spreadsheet and see a Count, per day of the closed tickets. Basically trying to determine easily what day my greatest number of tickets were closed.

    Thank you,
    Jeff

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,083

    Re: How do I determine Frequency of occurrences per each Calendar Date?

    Hi Jeff,

    It would appear this could be done with a pivot table, but how about a sample sheet.

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    HTH
    Regards, Jeff

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,312

    Re: How do I determine Frequency of occurrences per each Calendar Date?

    My first thought is to use a pivot table ( https://www.excel-easy.com/data-****...ot-tables.html if you are unfamiliar with pivot tables). Set up a pivot table with date closed as your row labels and count of ticket in the values field.

    Will that work for you?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    07-23-2019
    Location
    San Francisco, CA
    MS-Off Ver
    2013
    Posts
    9

    Re: How do I determine Frequency of occurrences per each Calendar Date?

    I can't attach files per company policy but here is a csv partial dump for the data.

    The resulting worksheet (with the Pivot table I guess) would show, by DATE CLOSED, the COUNT of the TICKETS

    TICKET,Assignment Group,Assignee,Status,Title,DATE CLOSED
    RM21217060,Directory Support,"Patron, Jeff",Closed,Internal Add DNS Request,5/15/2019 13:40
    RM21217078,Directory Support,"Patron, Jeff",Closed,Internal Add DNS Request,5/15/2019 13:44
    RM21216363,Directory Support,"Patron, Jeff",Closed,External Add DNS Request,5/16/2019 11:09
    RM21217478,Directory Support,"Patron, Jeff",Closed,Internal Change DNS Request,5/17/2019 13:21
    RM21203491,Directory Support,"Patron, Jeff",Closed,Internal Delete DNS Request,5/17/2019 14:31
    RM21216563,Directory Services DNS,"Patron, Jeff",Closed,Unknown Device - remove DNS server,5/17/2019 15:02
    RM21229404,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 10:43
    RM21229421,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 12:28
    RM21229384,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 12:38
    RM21235220,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 14:01
    RM21235325,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 14:12
    RM21235411,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 14:33
    RM21235948,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 14:42
    RM21235954,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 14:48
    RM21235965,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 15:10
    RM21235989,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 15:14
    RM21236009,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 15:20
    RM21236015,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 15:26
    RM21236016,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/21/2019 15:50
    RM21238063,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/22/2019 9:53
    RM21239085,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/22/2019 10:00
    RM21239114,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/22/2019 10:39
    RM21239467,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/22/2019 11:29
    RM21239500,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/22/2019 11:34
    RM21240199,Directory Services DNS,"Patron, Jeff",Closed,Internal Change DNS Request,5/23/2019 11:12
    RM21240213,Directory Services DNS,"Patron, Jeff",Closed,Internal Change DNS Request,5/23/2019 11:26
    RM21244243,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/23/2019 11:58
    RM21242231,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/23/2019 13:22
    RM21242264,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/23/2019 13:50
    RM21245069,Directory Services DNS,"Patron, Jeff",Closed,Internal Change DNS Request,5/23/2019 14:21
    RM21245096,Directory Services DNS,"Patron, Jeff",Closed,Internal Change DNS Request,5/23/2019 14:42
    RM21228681,Directory Services DNS,"Patron, Jeff",Closed,EZ Cap Upgrade - Change DNS Entries (5/17),5/24/2019 18:45
    RM21253313,Directory Services DNS,"Patron, Jeff",Closed,External Change DNS Request,5/27/2019 10:53
    RM21253316,Directory Services DNS,"Patron, Jeff",Closed,Internal Delete DNS Request,5/27/2019 12:03
    RM21253325,Directory Services DNS,"Patron, Jeff",Closed,Internal Delete DNS Request,5/27/2019 12:20
    RM21253587,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/27/2019 12:28
    RM21253838,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/27/2019 12:33
    RM21254311,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/27/2019 13:42
    RM21253998,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/27/2019 13:42
    RM21254339,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/27/2019 14:15
    RM21255750,Directory Services DNS,"Patron, Jeff",Closed,Internal Delete DNS Request,5/27/2019 15:26
    RM21257482,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/28/2019 9:50
    RM21257503,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/28/2019 9:59
    RM21257529,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/28/2019 10:09
    RM21251440,Directory Services DNS,"Patron, Jeff",Closed,Internal Delete DNS Request,5/28/2019 12:02
    RM21254049,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/28/2019 12:53
    RM21262464,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/29/2019 9:56
    RM21260744,Directory Services DNS,"Patron, Jeff",Closed,Internal Change DNS Request,5/29/2019 10:28
    RM21260525,Directory Services DNS,"Patron, Jeff",Closed,External Add DNS Request,5/29/2019 12:40
    RM21262461,Directory Services DNS,"Patron, Jeff",Closed,Internal Change DNS Request,5/29/2019 13:50
    RM21263231,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/29/2019 14:04
    RM21263242,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/29/2019 14:13
    RM21263315,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/29/2019 14:24
    RM21263310,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/29/2019 14:26
    RM21263438,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/29/2019 15:09
    RM21263460,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/29/2019 15:14
    RM21264262,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/29/2019 17:14
    RM21263429,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/30/2019 10:01
    RM21263723,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/30/2019 12:27
    RM21263729,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/30/2019 12:34
    RM21263732,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/30/2019 12:40
    RM21269157,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/30/2019 14:37
    RM21258924,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,5/30/2019 16:18
    RM21272025,Directory Services DNS,"Patron, Jeff",Closed,Internal Change DNS Request,5/31/2019 16:24
    RM21298535,Directory Services DNS,"Patron, Jeff",Canceled,Internal Add DNS Request,6/3/2019 10:06
    RM21298672,Directory Services DNS,"Patron, Jeff",Canceled,Internal Add DNS Request,6/3/2019 10:28
    RM21298660,Directory Services DNS,"Patron, Jeff",Canceled,Internal Add DNS Request,6/3/2019 10:29
    RM21272323,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,6/4/2019 9:54
    RM21279115,Directory Services DNS,"Patron, Jeff",Closed,Internal Change DNS Request,6/4/2019 10:38
    RM21271682,Directory Services DNS,"Patron, Jeff",Closed,Internal Change DNS Request,6/4/2019 11:08
    RM21268187,Directory Services DNS,"Patron, Jeff",Closed,Internal Add DNS Request,6/4/2019 11:23

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,083

    Re: How do I determine Frequency of occurrences per each Calendar Date?

    How about this pivot table.
    Attached Images Attached Images
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-23-2019
    Location
    San Francisco, CA
    MS-Off Ver
    2013
    Posts
    9

    Re: How do I determine Frequency of occurrences per each Calendar Date?

    Thanks Jeffrey, that's great and having it by day or month is even slicker. How do I learn to do your solve?

  7. #7
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,083

    Re: How do I determine Frequency of occurrences per each Calendar Date?

    You are very welcome and thanks for the feedback. I would start with the reference link MrShorty provided, but here's another one.

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,083

    Re: How do I determine Frequency of occurrences per each Calendar Date?

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  9. #9
    Registered User
    Join Date
    07-23-2019
    Location
    San Francisco, CA
    MS-Off Ver
    2013
    Posts
    9

    Re: How do I determine Frequency of occurrences per each Calendar Date?

    Jeffrey, I don't see how you added a "Months" Field as well as "Count" Field, which wasn't present in my original spreadsheet. I followed your link on Pivot tables but the explanation is not there either.
    Last edited by Zenmusicman; 07-23-2019 at 07:28 PM.

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,083

    Re: How do I determine Frequency of occurrences per each Calendar Date?

    In this case, it actually did it for me, but you want to look into grouping.

    https://www.contextures.com/xlPivot07.html

  11. #11
    Registered User
    Join Date
    07-23-2019
    Location
    San Francisco, CA
    MS-Off Ver
    2013
    Posts
    9

    Re: How do I determine Frequency of occurrences per each Calendar Date?

    Still doesn't explain "Count". How do you add that field?

  12. #12
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,083

    Re: How do I determine Frequency of occurrences per each Calendar Date?

    When you first make the pivot table you will add in the PivotTable Fields list...

    Rows >> Date Closed
    Value >> Status (The label should say Count of Status)

    Just drag the fields from the top box down to the four quadrants. Only the bottom two quadrants will have data. Rows and values

    Now in the Pivot Table itself, right click on a date and choose group. Highlight month and days then OK

    After you have group by months and days, not the Rows box in the PivotTable Fields list with have Months and Date Closed.

  13. #13
    Registered User
    Join Date
    07-23-2019
    Location
    San Francisco, CA
    MS-Off Ver
    2013
    Posts
    9

    Re: How do I determine Frequency of occurrences per each Calendar Date?

    Ok That got me in the ball park, thank you....however, I dragged as follows in the ROWS quadrant

    Yours:
    DATE CLOSED
    Months

    Mine:
    Months
    DATE CLOSED

    I don't see how you got that handy Total count per month. (In yours, this happens regardless how I order the ROWS quadrant).

    i.e.
    May Total
    Jun Total

  14. #14
    Registered User
    Join Date
    07-23-2019
    Location
    San Francisco, CA
    MS-Off Ver
    2013
    Posts
    9

    Re: How do I determine Frequency of occurrences per each Calendar Date?

    Jeffrey the other thing your Pivot has that mine doesn't is 3 columns. Mine only has 2 (Months and Count of Status)

    Maybe it's related to my earlier post but I like how you have columns as such:

    DATE CLOSED Months Count of Status

    The first two change as to how I order the ROWS quadrant but we still have 3 columns.

    All my PivotTable Fields are the same as yours

  15. #15
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,083

    Re: How do I determine Frequency of occurrences per each Calendar Date?

    For the month totals in the PT, highlight the PT, go to Design on the ribbon then to the far left under layout. Now adjust the Subtotals and Grand Totals. Turn them On and Off as you require.

    As for the 3 columns versus 2, if you download the posted sample file you can see what has been done.
    Attached Images Attached Images

  16. #16
    Registered User
    Join Date
    07-23-2019
    Location
    San Francisco, CA
    MS-Off Ver
    2013
    Posts
    9

    Re: How do I determine Frequency of occurrences per each Calendar Date?

    Thanks the subtotals (and grand totals) formatting worked for me. Very handy.

    I have exactly the same Fields as you (your screen shots) but still only show 2 columns.

    Would it perhaps be a PT formatting issue because Excel is displaying essentially one column,
    twice (on yours) and you've achieved a different view of the data.

    Your PT covers columns I(Months),J(DATE CLOSED),K(Count of Status). Mine, just I(Months) and J(Count of Status).

  17. #17
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,083

    Re: How do I determine Frequency of occurrences per each Calendar Date?

    Could it be that you need to change the report layout to tabular?

    Click on the PT and then on the ribbon

    Design >> Layout >> Report Layout >> Show in Tabular Form

  18. #18
    Registered User
    Join Date
    07-23-2019
    Location
    San Francisco, CA
    MS-Off Ver
    2013
    Posts
    9

    Re: How do I determine Frequency of occurrences per each Calendar Date?

    Voila! Thanks Jeff, I learned a lot, end-to-end.

    Closed thread.

  19. #19
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,083

    Re: How do I determine Frequency of occurrences per each Calendar Date?

    You are very welcome and thanks for the feedback.

+ 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. [SOLVED] Conditional Formatting: Highlight Occurrences between specified date range on a calendar
    By BayouRotor in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-06-2018, 03:08 PM
  2. [SOLVED] Trying to Determine the Frequency of Sales
    By hchurch in forum Excel General
    Replies: 4
    Last Post: 05-14-2018, 01:39 PM
  3. Determine frequency over time
    By gertrud in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-10-2016, 05:54 PM
  4. [SOLVED] How to determine 7 calendar days from a input date in another excel tab?
    By excelmr in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-11-2015, 12:55 PM
  5. Input a year to determine the start date for 4-4-5 Calendar Year.
    By mak1176 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-25-2013, 10:34 AM
  6. Histogram: What is the formula to determine the frequency
    By Elainefish in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-02-2013, 05:44 AM
  7. Match & Determine Frequency
    By mycon73 in forum Excel General
    Replies: 21
    Last Post: 08-03-2012, 02:17 PM

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