Comparing time range to decide the agent's shift

0

I have a task in which I need to decide how many agents are working in particular hour based on the time.

For example, if time is 7:00 AM to 5:00 PM, 4 agents would be working. If time is from 3 PM to 12 AM, 2 agents would be working (not considering the shift overlapping for now). I got the result set for all my timings/hours exept the shift time which ends the next day. For example, I have one shift starting 10:00 PM to 6:00 AM in which, I am not getting the correct result set. I am trying to find that during 10:00 PM to 6 AM, only one agent would be working. I have used IF(And(....)) conditions. However I am failing for this 10 PM to 6:00 AM timing. 

I have used =IF(AND(TIMEVALUE(TEXT(Y2,"hh:mm:ss "))>TIMEVALUE("22:00:00"),TIMEVALUE(TEXT(Y2,"hh:mm:ss"))<=TIMEVALUE("6:00:00")),1,0)

(Note : In the above formula, Y2 is the time in HH:MM:SS 24 hours format)

Can anyone help me providing me the correct formula or correct condition which overcome this midnight shift timing issue?

Answer
Discuss

Answers

0
Selected Answer

The TIMEVALUE function converts a text string into a number. In the case of TIMEVALUE("22:00") the number is 0.916666667. For  for 6 AM it's 0.25. Calculate 1 / 24 * 6, meaning [1 day] / [24 hours] * [6 o'clock]. If you wish to indicate tomorrow's 6 AM you must add 1 day. Therefore 1.25 will be tomorrow's 6 AM. You could create such a number with the formula = TIMEVALUE("6:00") + 1

=TODAY() will give you a number like 42964. =NOW() will return a number like 42964.80554. Accordingly, todays's 10 PM would be 42964.91666667 and tomorrow's 6AM will be 42965.25. If you actually have such numbers everything is very easy. More than half of your problem comes form having text which looks like numbers instead of numbers which are displayed like text.

So, learn about number formats. In a new blank worksheet all cells are formatted as "Gneral". In a cell with this format, enter 42964.91666667. You will see the number. Now change the number format (Format > Cells > Numjber > Custom) to "dddd" (without quotation marks) and your cell will display "Thursday". Format it as "dd mmm" (without quotation marks) and you will see "17 Aug". Set the format as "hh:mm" (without quotation marks) and you will see "22:00". Let the format be "dd/mm/yyyy hh:mm" (without quotation marks) and the cell will show "17/08/2017 22:00"

The same system works the other way around, too. In a cell formatted with "hh:mm" enter "18/8/17 6:00" (no quotation marks) and the cell will display "06:00". The date will be hidden, but the cell will hold the complete number. You can format it as "General" and see the actual number. In this way tomorrow's 6 AM can be made different from today's 6 AM although both cells show the same "06:00".

You can't read these cells with TIMEVALUE or DATEVALUE. Instead you treat them like numbers, for example,

=IF(AND(Y2 >(TODAY() + (1/24*22)),Y2<(TODAY()+1+(1/24*6))),1,0)

This formula requires Y2 to hold a true Date/Time value like =Now()

Based upon your revised workbook I suggest another approach. Try this formula in B3.

=COUNTIFS(Start,"<=" & MOD($A3,1),End,">=" & MOD($A3,1))

Before this formula can work you should do two thigs, however. First, create two named ranges. F4:F8 = "Start" and G4:G8 = "End". This serves to make your formula more readable and changes easier to manage. Second, instead of entering 6:00 in G8 enter 30:00. That changes the cell's value from 0.25 to 1.25 with the implication that it is the next day's time.

Discuss

Discussion

Hello Variatus,

Thank you for checking and addressing my first question nicely on TeachExcel !

I tried implementing your provided solution in my spreadsheet. However, I am unable to get the expected results. I also checked the format of each cells for which the formula was referenced.

Here, I am sharing my spreadsheet containing a sample available data explaining the expected results which may give you clear idea of my expectation.

(1) Columns/Cells from E3:H8 contains the shift timings (Start and End) of each agents.
(2) Columns / Cells from E12:G36 contains the number and name of the agents available at any particular hour of the day.
(3) I have a list of date/time of the inbound call for particular week which is shown in Column A. I am interested to find the number of agents that were available at the time at which the call was received in Column B based on the Chart shown in above point #1.

Issue : If you analyse the data in the spreadsheet, you will observe that Ken's shift start at 10:00 PM on first day and ends at 6:00 AM on second day. Here, I am unable to get the accurate results as the shift ends on the second day or the shift start time is greater than the shift end time.

Could you check this again and help me with any solution?

(Note : The SampleData.xlsx spreadsheet is attached in the original question/post.)
exltech (rep: 2) Aug 17, '17 at 1:12 pm
Hello Variatus,

I was able to achive my expectated results based on your advise to play with numbers for date/time formats. One thing I noticed that the number is getting reset to zero at 12:00 AM which is failing my If condition to calculate the number of agents. So I have to divide Ken's shift into two different parts i.e. (From 22:00 To 23:59) and (From 00:00 To 06:00) which resolved this issue.

I appreciate you addressing my query and your valuable advise!

Thank you!
exltech (rep: 2) Aug 23, '17 at 8:25 am
I'm very interested in this: Did you set the 6AM value to "30:00" (without quotation marks)? The cell will still show 06:00 but the value will be 1.25 instead of 0.25. This value shouldn't ever change. You say it changes at midnight. That's the part that has me astonished and I wonder if you can confirm it. Thank you.
Variatus (rep: 1423) Aug 23, '17 at 9:48 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login