• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula to calculate the ticket count within a time slot

Hi Gurus


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
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

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.
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

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


  • Excel.1-ETAF.xlsx
    10.8 KB · Views: 2