Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 84
  • Last Modified:

Count the number of occurances of a ticket grouped by month and then day of the week.

I have a list of ticket numbers in column A and the date and time the ticket was opened in Column b.  I want to create a report that groups the ticket by Month and then counts the number of tickets that were opened on Monday thru Sunday and count them by two time frames between 6 am to 6 pm and 6 pm to 6am.  I have tried a countifs formula unsuccessfully and then I tried a pivot chart and wasn't able to get that work either.  Any ideas?
0
Rrave26
Asked:
Rrave26
  • 5
  • 5
  • 3
1 Solution
 
Saqib Husain, SyedEngineerCommented:
How about a sample for working?
0
 
Rrave26Author Commented:
I am dealing with column B, incident number, and column AQ, Outage start combined.
Metrics-Tracking-Database.xlsx
0
 
Glenn RayExcel VBA DeveloperCommented:
In order to be able to summarize the data using a PivotTable and group by months and weeks as you requested, I augmented your original data table by adding two new columns.  One returns the day of the week ("Outage Start Day") and one determines the shift ("Outage Start Time Shift").  The shift returns "Day" or "Night", depending on if the time is between 6AM-6PM (day) or 6:00PM-6:00AM (night).

I then created a PivotTable that groups the Outage Start Date Combined by months, then shows seven day groups, each covering a Monday-Sunday period.  Below each is the shift.  The ticket counts are show in the Values section.

If your needs are different, please detail them and I'll try to help.

Regards,
-Glenn
EE-Metrics-Tracking-Database.xlsx
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Rrave26Author Commented:
This is good but I am looking for something slightly different.  Instead of the 7 day groups what I would like to see in the Columns is Sun, Mon, Tue, Wed,Thur, Fri and Sat.  Then the counts underneath each day of the week that are grouped by month and then day or night as the rows.

It would hopefully look like this:

          Sun   Mon  Tue  Wed  Thur  Fri  Sat
Month
 day     4        7
Night    1       1

I hope that is clear.
0
 
Saqib Husain, SyedEngineerCommented:
Try this file which uses two different formulas for day and night

=SUM(IF((MONTH($A7)=MONTH(Sheet1!$AQ$2:$AQ$10000))*(YEAR($A7)=YEAR(Sheet1!$AQ$2:$AQ$10000))*(WEEKDAY(Sheet1!$AQ$2:$AQ$10000)=Sheet3!C$5)*(ABS((Sheet1!$AQ$2:$AQ$10000)-INT(Sheet1!$AQ$2:$AQ$10000)-0.5)<0.25),1,0))

=SUM(IF((MONTH($A7)=MONTH(Sheet1!$AQ$2:$AQ$10000))*(YEAR($A7)=YEAR(Sheet1!$AQ$2:$AQ$10000))*(WEEKDAY(Sheet1!$AQ$2:$AQ$10000)=Sheet3!C$5)*(ABS((Sheet1!$AQ$2:$AQ$10000)-INT(Sheet1!$AQ$2:$AQ$10000)-0.5)>0.25),1,0))
Metrics-Tracking-Database.xlsx
0
 
Glenn RayExcel VBA DeveloperCommented:
Okay, how about this example?

I changed the "Outage Start Day" to return an actual string value for "Mon" through "Sun" (it originally was the same date as "Outage Start Combined" but re-formatted as the day string).  Then I added that field to the Column Labels in the PivotTable.  Finally, I changed the sort option for the column to manual so I could move "Sun" to last column (it sorted Sun-Sat by default).

I finally re-grouped the "Outage Start Date" by Years and Months (noticed that December occurs in 2013 and 2014).


Regards,
-Glenn
EE-Metrics-Tracking-Database.xlsx
0
 
Glenn RayExcel VBA DeveloperCommented:
The array formula is not complete.  It does not account for all times between 6:00:01 AM and 6:00:00 PM (0.250011574-0.75) as one group and 6:00:01 PM and 6:00:00 AM (0.750011574 - 0.25) completely.  

For example, in December 2013, there are four "day" tickets and two "night" tickets; the array formulas return only 2 and 1, respectively.  See the example file above.

-Glenn
0
 
Saqib Husain, SyedEngineerCommented:
In December 2013 I can see only three tickets for Sunday.
0
 
Glenn RayExcel VBA DeveloperCommented:
In my example file (which I based on the original), the following rows are December dates that fall on Monday:
2, 9, 10, 11, 27, 32
and their shifts are:
Day, Night, Day, Day, Night, Day

Am I missing something?
0
 
Saqib Husain, SyedEngineerCommented:
Is that not what I have? 4 + 2 for Monday?
0
 
Glenn RayExcel VBA DeveloperCommented:
My apologies, Syed.  Your formulas are absolutely correct.

I was confused since you had Sunday as your first column and the requester was asking for Monday-Sunday as the week range.  Transposing the days fixes this and the formulas hold.

-Glenn
0
 
Saqib Husain, SyedEngineerCommented:
Glenn Ray, no problem, again if you look at the desired output posted by the asker later it shows starting Sunday.
0
 
Rrave26Author Commented:
Thanks for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now