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?
Rrave26Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.