Grouping Time in Start-End Interval

Hi All,

I have been googling for past few days and with failure generating the report i desire. Please teach me the formula for following;

>> I have hundreds of tickets with date/time stamped. I want to group it according this time pattern i.e 8am-5pm , 5pm-2am , 2am-8am . I used this formula =IF(AH2>AH3,AH3+1-AH2,AH3-AH2) but i guess its wrong.

please advice.

PKK

Hello
Are you looking to do a count of tickets between each time frame?

yes that right. looking to sum the count of ticket between each time frame.

Hi
I'll mock up a spreadsheet with some example data to give you an idea as to how to proceed. Much depends upon how your data of ticket time stamps is laid out.

this is my spreadsheet looks likemy ticket.xlsx

Take a look at the attached reply. I've had a bit of trouble as the times pass midnight so I've had to compensate for that also your times are included with the date. Check the results for accuracy.

DBY, thats is accurate. Thanks alot.

I then tried customizing it and hit "0" on time frame 4pm - 12am. what could be wrong here?

12am - 8am SUMPRODUCT((MOD(AE2:AE524,1)>=AQ3)*(MOD(AE2:AE524,1)<AQ4))

8am -4pm SUMPRODUCT((MOD(AE2:AE524,1)>=AQ5)*(MOD(AE2:AE524,1)<AQ6))

4pm - 12am SUMPRODUCT((MOD(AE2:AE524,1)>=AQ7)*(MOD(AE2:AE524,1)<AQ8))

What values do you have in the cells: AQ3; AQ4; AQ5; AQ6; AQ7; and AQ8?. The way you have that set out, they should be respectively: 00:00; 08:00; 08:00; 16:00; 16:00; and 1. However, I would have them like this as you're now not crossing midnight:

00:00	Formulas Column
08:00
16:00
1

Remember these must be Time Values, the 12am - 8am cells are labels only.

Yes it does not cross midnight so literally the formula is the same for all column. I tried and hitting same result "0" and this time the total sum doesn't tally.my ticket_new time frame.xlsx

Hi
Take a look at the amended version of your uploaded file. It makes some corrections to what both you and I had been doing. Please check for accuracy.

DBY, thanks a bunch man. you are my hero.... thank you.....

Glad to have helped.

hi , how can we separate those pulled data based on time frame into month,date,year?

Hi
Perhaps as in the amended attachment?

Try the new amended file. Perhaps this is closer? It has a scroll for the Days to save space.

DBY