Formula to calculate the ticket count within a time slot

[Vignesh Veerasamy]

Hi Gurus

Greetings.

Formula to calculate the ticket count within a time slot.
We are working based on tickets and we are able to get overall ticket count but for one MIS report they need to find out for an hour how many tickets are received per day.Kindly share inputs.

Vignesh V

 

[ETAF]

what version of excel

so you want for example - ALL the tickets that are in the system for a 1 hour time slot- say 9-10am BUT only for 1 particular date
OR for all the data , by Hour - so how many tickets for each hour of the a day for the whole data set
00:00 - 01:00 am
01:00:01 - 02:00 am
etc


A sample sheet would help here, and possibly will enable a quicker and more accurate solution for you.
A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

 

[Vignesh Veerasamy]

Sample data output is like below

 

[pecoflyer]

Please post a sample sheet ( pics cannot be worked upon). Thx

 

[Vignesh Veerasamy]

Sorry pl find attached.

 

[ETAF]

do you calculate or enter the shift type - or does that need to be calculated
if calculated what are the ranges of time for those 3 shifts - make sure no time range overlaps

if you are entering then
=COUNTIF($C$3:$C$6,E$4)

otheriwse to calculate the shift
=IF((B3-INT(B3))<TIMEVALUE("12:00:00"),"Morning shift", IF((B3-INT(B3))<TIMEVALUE("18:00:00"),"second Shift","third Shift"))
I have used 12:00 and 18:00 as the shift changes
change those time to match you data