Call Centre Data - Assign Time Period Category to inbound call Start Time.

[lj97]

Hi all!

Another questing regarding the call centre data I am working with.

Similar question to my last post, thanks again to Joe4 for their awesome response/formula!

In this problem, I am needing to assign a Time Period Category (column A) to the Start Time (column B) of the inbound call to the centre.

In column E, there is the list of the relevant Time Period Categories that need to be specified depending on the call Start Time.

I have attempted to complete this myself using the FLOOR function to no avail; this seems to be somewhat above my current (very beginner) skill level.

Any assistance or solutions would be greatly appreciated!

Book1
	A	B	C	D	E
1	Time Period Category	Start Time			LIST - Time Period Category
2		02:10			1 am - 5 am
3		07:34			5 am - 9 am
4		11:27			9 am - 1 pm
5		15:38			1 pm - 5 pm
6		08:43			5 pm - 9 pm
7		00:07			9 pm - 1 am
Sheet1 (2)

 

[Alex Blakenburg]

I tend to prefer to use lookup tables for this sort of thing.
See if you are interesed in doing it this way.

20220720 Time In Range Categories.xlsx
	A	B	C	D	E	F
1	Time Period Category	Start Time			LIST - Time Period Category
2	1 am - 5 am	02:10			Start Value	Category
3	5 am - 9 am	07:34			00:00	9 pm - 1 am
4	9 am - 1 pm	11:27			01:00	1 am - 5 am
5	1 pm - 5 pm	15:38			4:00	5 am - 9 am
6	5 am - 9 am	08:43			9:00	9 am - 1 pm
7	9 pm - 1 am	00:07			13:00	1 pm - 5 pm
8					17:00	5 pm - 9 pm
9					21:00	9 pm - 1 am
10
Sheet1

Cell Formulas
Range	Formula
A2:A7	A2	=VLOOKUP(MOD(B2,1),$E$3:$F$9,2,TRUE)

 

[Peter_SSs]

@Alex Blakenburg
Hi Alex
Does the MOD add anything since times are already fractional?

22 07 20.xlsm
	A	B	C	D	E	F
1	Time Period Category	Start Time			LIST - Time Period Category
2	1 am - 5 am	02:10			Start Value	Category
3	5 am - 9 am	07:34			00:00	9 pm - 1 am
4	9 am - 1 pm	11:27			01:00	1 am - 5 am
5	1 pm - 5 pm	15:38			04:00	5 am - 9 am
6	5 am - 9 am	08:43			09:00	9 am - 1 pm
7	9 pm - 1 am	00:07			13:00	1 pm - 5 pm
8					17:00	5 pm - 9 pm
9					21:00	9 pm - 1 am
Time Period

Cell Formulas
Range	Formula
A2:A7	A2	=VLOOKUP(B2,E$3:F$9,2,TRUE)

 

[Alex Blakenburg]

@Peter_SSs I was just concerned about the 9pm - 1am potentially resulting in the day part ticking over, I guess it is unlikely. I just don't know anything about how the data gets in there.

 

[lj97]

Thank you both for your input!

Appreciate the first response Alex. I haven't seen the MOD function before so I will have a look into that

 

[Peter_SSs]

Thank you both for your input!

You're welcome. Glad we could help. Thanks for the follow-up.