SOLVED

Counting Time Periods

Copper Contributor

  Not sure if this is possible, but I thought would ask. In the below example I need to find out how many times a Tech left a call and then in less than 30 minutes later, that line called again. For example on 10/22/2024 Tech B left line 1 at 1012(military time) and line 1 called again at 1030. This will add 1 to Tech B total. So in this example Tech A = 1, Tech B = 4, Tech C = 2.

  Any help with this would be greatly appreciated. I have a very large spreadsheet that I would like to extract this information from.

 

LINEDATECALL TIMEOUT TIMETECH
110/22/2024823845A
110/22/20249551012B
210/22/202410301035C
310/22/2024830840C
110/22/202410301055A
210/22/202410451115A
210/23/202414251450B
310/23/202412551310B
210/23/202415101530C
310/23/202413301345A
210/23/202415451650A
110/24/2024750830C
110/24/2024910930B
310/24/2024820835A
310/24/2024850920A
110/24/20249551020B
     
5 Replies
Hello,
just to clarify, if a tech receives a call from the same line within 30 minutes of finishing the previous call, this counts as 1 for the tech. Is this understanding correct?
best response confirmed by Tspears1 (Copper Contributor)
Solution

@Tspears1 The first step is to convert the numbers representing military time to an actual date/time value using the TIME function with INT and MOD to extract the hours and minutes. For example:

 

CALL DATE/TIME (column F):
=B2+TIME(INT(C2/100), MOD(C2, 100), 0)

OUT DATE/TIME (column G):
=B2+TIME(INT(D2/100), MOD(D2, 100), 0)

 

Then, COUNTIFS can be used to determine if each call had a callback within 30 minutes:

 

=N(COUNTIFS($A$2:$A$17, A2, $F$2:$F$17, ">="&G2, $F$2:$F$17, "<="&G2+30/1440)>0)

 

Count callbacks within 30 minutesCount callbacks within 30 minutes

 

Please see the attached workbook for the complete solution...

Yes, but it could be a different tech that gets that call.@Rodrigo_ 

Yes! This works great! Thank you. You are the best!
Glad to hear it! You're welcome :)
1 best response

Accepted Solutions
best response confirmed by Tspears1 (Copper Contributor)
Solution

@Tspears1 The first step is to convert the numbers representing military time to an actual date/time value using the TIME function with INT and MOD to extract the hours and minutes. For example:

 

CALL DATE/TIME (column F):
=B2+TIME(INT(C2/100), MOD(C2, 100), 0)

OUT DATE/TIME (column G):
=B2+TIME(INT(D2/100), MOD(D2, 100), 0)

 

Then, COUNTIFS can be used to determine if each call had a callback within 30 minutes:

 

=N(COUNTIFS($A$2:$A$17, A2, $F$2:$F$17, ">="&G2, $F$2:$F$17, "<="&G2+30/1440)>0)

 

Count callbacks within 30 minutesCount callbacks within 30 minutes

 

Please see the attached workbook for the complete solution...

View solution in original post