How to create buckets using time

[farheenayesha]

Hi,

 I want to create four buckets as below using the Closed in time field. 

1. <=5PM -Wd2 ( Starts from 12AM of WD+1 to 5PM  of WD+2)
2. >5PM - Wd2  ( Starts after 5PM of WD+2 to 12 AM of  WD+3)
3. <=5PM - Wd3  ( Starts from 12 AM of WD+3 to 5 PM of  WD+3)
4. >5PM - Wd3  ( Starts after 5PM of WD+3)

I have three workdays WD+1,WD+2 and WD+3. I want to calculate the Count of ID's for these four time slots in bar graph. Please find the data attached and please help me to get the result.

Ex: for this group <=5PM -Wd2 what is the count of ID's. Similarly for all four groups.

@sunny_talwar 

[sunny_talwar]

Would you be able to specify the expected output based on the data provided? and may be explain it a little

[farheenayesha]

Hi Sunny,

I have added month field in the attached data. Please consider the new data file attached.

 Ex. If i take the  data only for <=5PM -Wd2 and Jul Month, which includes records having time less than 5PM, then the count of ID's should be 98.  Please find the data below.  also attaching the complete data.

Closed in WD	Closed in Time	ID	Month
WD+2	4:50 PM	AT01	July'18
WD+2	4:50 PM	AT02	July'18
WD+2	4:56 PM	DE19	July'18
WD+2	4:46 PM	DE25	July'18
WD+2	4:30 PM	DE28	July'18
WD+2	3:10 PM	ES09	July'18
WD+2	4:58 PM	FR05	July'18
WD+2	4:07 PM	GB66	July'18
WD+2	2:26 PM	IE01	July'18
WD+2	4:07 PM	IE04	July'18
WD+2	4:07 PM	IE05	July'18
WD+2	4:23 PM	IE10	July'18
WD+2	4:30 PM	IE12	July'18
WD+2	4:23 PM	IE16	July'18
WD+2	3:12 PM	IE17	July'18
WD+2	3:12 PM	IE18	July'18
WD+2	3:12 PM	IT01	July'18
WD+2	3:12 PM	IT02	July'18
WD+2	3:12 PM	IT03	July'18
WD+2	3:12 PM	IT04	July'18
WD+2	3:09 PM	NL01	July'18
WD+2	2:46 PM	NL06	July'18
WD+2	2:46 PM	NL08	July'18
WD+2	2:46 PM	NO01	July'18
WD+2	2:46 PM	NO02	July'18
WD+2	2:46 PM	NO04	July'18
WD+2	2:46 PM	PL01	July'18
WD+2	2:46 PM	PL03	July'18
WD+2	2:46 PM	PT01	July'18
WD+2	2:46 PM	PT02	July'18
WD+2	2:11 PM	PT04	July'18
WD+2	5:00 AM	PT07	July'18
WD+2	5:00 AM	PT08	July'18
WD+2	5:00 AM	US46	July'18
WD+2	5:00 AM	US50	July'18
WD+2	4:00 PM	USAF	July'18
WD+2	4:30 PM	FR09	July'18
WD+2	4:30 PM	GB02	July'18
WD+2	5:00 PM	GB06	July'18
WD+2	5:00 PM	GB08	July'18
WD+2	5:00 PM	GB09	July'18
WD+2	4:00 PM	GB19	July'18
WD+2	5:00 PM	GB1A	July'18
WD+2	5:00 PM	GB20	July'18
WD+2	5:00 PM	GB21	July'18
WD+2	5:00 PM	GB22	July'18
WD+2	4:30 PM	GB23	July'18
WD+2	5:00 PM	GB24	July'18
WD+2	4:30 PM	GB25	July'18
WD+2	5:00 PM	GB26	July'18
WD+2	5:00 PM	GB30	July'18
WD+2	5:00 PM	GB31	July'18
WD+2	4:30 PM	GB33	July'18
WD+2	4:30 PM	GB34	July'18
WD+2	4:00 PM	GB35	July'18
WD+2	5:00 PM	GB38	July'18
WD+2	4:00 PM	GB39	July'18
WD+2	4:30 PM	GB40	July'18
WD+2	4:00 PM	GB41	July'18
WD+2	4:00 PM	GB42	July'18
WD+2	4:00 PM	GB44	July'18
WD+2	4:00 PM	GB45	July'18
WD+2	4:00 PM	GB47	July'18
WD+2	4:00 PM	GB49	July'18
WD+2	4:00 PM	GB55	July'18
WD+2	4:00 PM	GB58	July'18
WD+2	5:00 PM	GB59	July'18
WD+2	4:00 PM	GB5Q	July'18
WD+2	4:00 PM	GB64	July'18
WD+2	4:00 PM	GB70	July'18
WD+2	4:00 PM	GB71	July'18
WD+2	4:00 PM	GB73	July'18
WD+2	4:00 PM	GB74	July'18
WD+2	4:00 PM	GB78	July'18
WD+2	4:00 PM	GB80	July'18
WD+2	5:00 PM	GB81	July'18
WD+2	4:30 PM	GB82	July'18
WD+2	4:00 PM	GB83	July'18
WD+2	4:00 PM	GB84	July'18
WD+2	4:00 PM	GB87	July'18
WD+2	4:00 PM	GB88	July'18
WD+2	4:00 PM	GB89	July'18
WD+2	4:00 PM	GB91	July'18
WD+2	4:00 PM	GB93	July'18
WD+2	4:00 PM	GB94	July'18
WD+2	4:00 PM	GB95	July'18
WD+2	4:00 PM	GBAJ	July'18
WD+2	4:00 PM	GBAL	July'18
WD+2	4:00 PM	GBAU	July'18
WD+2	4:00 PM	GBAV	July'18
WD+2	4:00 PM	GBAY	July'18
WD+2	4:00 PM	GBBC	July'18
WD+2	4:00 PM	GBBF	July'18
WD+2	4:30 PM	GBCD	July'18
WD+2	4:00 PM	GBCE	July'18
WD+2	4:00 PM	GBCF	July'18
WD+2	4:30 PM	GBCG	July'18
WD+2	04.03 AM	ID04	July'18