Time analysis - Counting time (hours) - counting each time stamp

[st471438]

Good morning everyone,

I am back to this amazing forum that has given a LOT to excel community and thank you everyone for that

So I have this spreadsheet where it is basically call center data so in one of the column it shows time of inbound call as example (21:12:05) and other columns shows specific notes about that call example if product was sold on that call or not. So I am trying to find trend that during what period of time in a day maximum product was sold. Example I want to set some criteria that from 8 am to 10 am, 10 am to 1 pm and so on and then to find how many products are sold. So what I am looking is how can i extract hours as numbers only? from time stamp? I tried using =RIGHT(a1,2) but its not working and it messing with the number format.

What is the easy way to accomplish this?

I thank you once again in advance.

 

[T. Valko]

Good morning everyone,

I am back to this amazing forum that has given a LOT to excel community and thank you everyone for that

So I have this spreadsheet where it is basically call center data so in one of the column it shows time of inbound call as example (21:12:05) and other columns shows specific notes about that call example if product was sold on that call or not. So I am trying to find trend that during what period of time in a day maximum product was sold. Example I want to set some criteria that from 8 am to 10 am, 10 am to 1 pm and so on and then to find how many products are sold. So what I am looking is how can i extract hours as numbers only? from time stamp? I tried using =RIGHT(a1,2) but its not working and it messing with the number format.

What is the easy way to accomplish this?

I thank you once again in advance.

The HOUR( ) function will return the hour number.

A2 = 21:12:05

=HOUR(A2)

Returns the integer 21.

Note that 12:00 AM is hour 0.

 

[st471438]

Awesome thank you so much. Now I have another problem.

Hours are in one column and in another column it says "sale" and many other values but I want to count "sale" during the certain group of hours.

So let's say how many time sale comes during hours 12 to 14, 18 to 20 etc. Does this make sense?

 

[T. Valko]

Awesome thank you so much. Now I have another problem.

Hours are in one column and in another column it says "sale" and many other values but I want to count "sale" during the certain group of hours.

So let's say how many time sale comes during hours 12 to 14, 18 to 20 etc. Does this make sense?

Sure, we can do that.

What version of Excel are you using?

 

[emisa042185]

hi biff, st471439,

i also have the same question on this thread, however, there is a quite little difference.

I am also looking for data to be counted on a certain time interval. Say first column has time stamps of employee went on break, coaching, or lunch. By the way, it's a callcanter data too. Second column has the activity of people went on break, lunch, or coaching. And third, is the department where they belong, say AOL, and NortonLive.

I used this formula =COUNTIFS(PST,">="&$M3,PST,"<"&$N3,sheetqueue,"="&$E$1,act,"="&$E$11), with the pre-defined range name, of same range size to make it uniform, but i only get 0 value.

thanks for your help.