formula

[Afarag]

Hello,

please i need help in a formula that might be complicated somehow,
in recap, this sheet have Username, Interval, Chat start time An Chat end time,
i need to export the available time that the agent haven't any chatting time, therefore the agent may have 2 or 3 chat at once,

i have used the below formula to get results, but face a miscalculation,
=IFERROR(IF(COUNTIFS($A$2:$A$6913,A32,$E$2:$E$6913,"<="&D32,$F$2:$F$6913,">="&D32+15/1440),0,15/1440-(MIN(SUMPRODUCT(MAX(($A$2:$A$6913=A32)*($D$2:$D$6913=D32)*($F$2:$F$6913))),D32+15/1440)-MAX(D32,SUMPRODUCT(MIN(100*($A$2:$A$6913<>A32)+100*($D$2:$D$6913<>D32)+($E$2:$E$6913)))))),"")

as shown at interval "11:30:00", the first chat was at "11:32:00", the available time must be 2 minute as per the rest of interval is occupied,

another example: at interval "12:15:00", i have 1 minute as an available time while there is a chat that started from 12:00:00 till 12:26:00,

note: some chats have a long duration, and cross more than one interval need to be considered in the formula,

is that solvable?
we can use Formula or VBA


Gratefully,

 

[Luke M]

Can you elaborate on how to read your spreadsheet? "Interval" usually refers to a duration of time, but I don't think that's how your sheet is using the word. Further, you have two columns which are both labeled Inteval, so I'm not sure which one we should be looking at. I see 6 rows which have an Interval of "11:30" listed, and one has a mark in the 2nd inerval column (what does that mean?).

 

[Afarag]

Hi Luke,

the interval in C column; each interval may be duplicated as each unique user name as per every employee may take more than one chat in the interval, my request is to extract the sum of the available time for every interval, so that in column "D", i want to remove the duplication of interval to get the sum of the available time beside the interval for each agent,

but any way this isn't solve my problem, therefore my above formula didn't get right results,

you can use the Interval in column "C"

Thanks a lot,

 

[Luke M]

One more questions. I can see that col E and F are the start/end times of calls. How do we know when the start/end of a person's shift (interval??) is? If I see an interval of 9:45:00, am I only worried about the amount of time till their first call (so, if they had a call from 9:48 - 10:20 answer would be 0:03:00), or do we care about the time at the end of the interval as wel? In which case, answer would be ____?

 

[Afarag]

Hello Luke,

1st point about user's Shift time or interval, if what about user shift i have this shifts from another report, but if the question related to how can we get the interval, i get it from the start time, i rounded the start time to nearest interval by this formula in col C

=(HOUR(E2)/24+CEILING(MINUTE(E2),15)/(24*60))-"0:15:00"

interval of 9:45:00, am I only worried about the amount of time till their first call (so, if they had a call from 9:48 - 10:20 answer would be 0:03:00), or do we care about the time at the end of the interval as wel? In which case, answer would be ____?

i need to deal with each interval separately, at interval 9:45:00 if i had a chat at
9:48:00 the available time should be 3 minutes, unless if i hadn't a large chat at the previous interval.

hope if it make sense.

i uploaded the last sheet that have the agents shift time

Gratefully,

 

[Luke M]

Take a look at this. Went with a UDF that's pretty powerful. Realized that with your new table, might only need overall down time, rather than at each interval.

 

[Afarag]

Hello Luke,

really thanks a lot for following, excuse me about confusion i can't apply the built-in UDF, "DATAGAP", i read the code but didn't get the UDF sequence for "missingCells"

to reduce the calculation overhead you can apply the UDF for one Agent, and i'll apply for the all,

thanks a lot,

 

[Luke M]

That's...what I did. The formula is in cell M2. There shouldn't have been anything you needed to do.

How the UDF works:
Think of a giant timeline. We are checking off the points on that timeline that a person has calls, and then at the end, we want to know how many points on the timeline remain.
In VB, I build the timeline using a Range. I had to do a little conversion since ranges are whole numbers and minutes are decimals, but it still works. Anyway, we build ShiftRange which is the timeline of all the time your agent is available. We then build another timeline, the GapRange, which consists of all the little blocks of calls.
The last timeline is the IntRange, which checks where the two previous timelines overlap. Now, if agent was busy 100% of the time, this IntRange should be same size as ShiftRange. If it's not, and it's actually smaller, then whatever the difference in size bewteen ShiftRange and IntRange is how many minutes were not filed up with calls.

 

[Afarag]

Hi Luke,

appreciate your effort, sorry i wasn't see the modification, if i need the free time to be exported per interval, is that available?,
as i need this data as a historical for forecasting per interval level, i need to know the busy interval and the free one to cover them and make a schedule distributing as per the interval pattern.
so that the interval level outcomes is very important for my calculations.

 

[Luke M]

Sure thing. If you look at the function, the arguments are:
NameRange (Data Range with names)
xName (the name you want to look for)
StartRange (Range of start time for calls)
EndRange (Range of end time for calls)
StartTime (Beginning of the time period you want to know about)

The last argument is optional, the EndTime. This would be the end of the time period you want to look at. If it's omitted, EndTime is assumed to be 15 minutes after StartTime.

So, yes, you can easily adjust the formula to be any interval you want. In the attached, I made a new table just for one person, and you can see how the formula looks at each interval.

 

[Afarag]

Yes, you are right, i was confused some how, but now i got it totally

Thanks a lot,

 

[Afarag]

@Luke M

sorry to back to this thread again after this long duration because i depend on this calculation too much,

in recap, this sheet that get the free time (Gap) between "Last time" with "Start time), but there is something go wrong and i can't got it

as shown

the colored cells refer to the wrong occurred values. As the UDF results return in 0 while their is a free time as per the gap between "Last time" with "Start time)

 

[Luke M]

Hi Afarag,

The problem is being caused by the data in row 67, where we have a time span that crosses midnight. The UDF wasn't originally built to handle times in two different days, so it was reading that line as if someone worked from 1:16 AM to 23:38 PM in the same day. This then was causing all the intervals to not show up, as the person was very busy.

I changed the function to have a new checkEndTime variable to adjust if such a thing happens.

Function DataGap(NameRange As Range, xName As String, StartRange As Range, EndRange As Range, StartTime As Date, Optional EndTime As Date) As Date

Dim GapRange As Range
Dim ShiftRange As Range
'how many seconds in a day?
Const xConv As Long = 86400
Dim intRange As Range
Dim i As Long
Dim missingCells As Long
Dim checkEndTime As Date

If EndTime = 0 Then
    'Calculate end of interval
    EndTime = StartTime + TimeValue("0:15:00")
End If



'Build shift range
Set ShiftRange = Range(Cells(StartTime * xConv, 1), Cells(EndTime * xConv, 1))


'Build filled range
For i = 1 To NameRange.Cells.Count
    If NameRange.Cells(i).Value = xName Then
        checkEndTime = EndRange.Cells(i).Value
        'Add 1 if checkendtime is earlier than start time, to indicate it's in the next day
        'In VB, True = -1, so we subtract
        checkEndTime = checkEndTime - (checkEndTime < StartRange.Cells(i).Value)
       
        If GapRange Is Nothing Then
            Set GapRange = Range(Cells(StartRange.Cells(i) * xConv, 1), Cells(checkEndTime * xConv, 1))
        Else
            Set GapRange = Union(GapRange, Range(Cells(StartRange.Cells(i) * xConv, 1), Cells(checkEndTime * xConv, 1)))
        End If
    End If
Next i

If Not GapRange Is Nothing Then
    Set intRange = Intersect(GapRange, ShiftRange)
End If
If intRange Is Nothing Then
    missingCells = ShiftRange.Cells.Count - 1
Else
    missingCells = ShiftRange.Cells.Count - intRange.Cells.Count
End If

DataGap = missingCells / xConv
End Function

and the calculations appear to be working now. I'm not sure about cell H34:I34, if the result is correct, but I think it is.

 

[Afarag]

Hello Luke M,

yes you are right about the problem cause, i checked the provided update from you and everything go well

thank you so much for your support.