SOLVED

# Counting Time Periods

Copper Contributor

# Counting Time Periods

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.

 LINE DATE CALL TIME OUT TIME TECH 1 10/22/2024 823 845 A 1 10/22/2024 955 1012 B 2 10/22/2024 1030 1035 C 3 10/22/2024 830 840 C 1 10/22/2024 1030 1055 A 2 10/22/2024 1045 1115 A 2 10/23/2024 1425 1450 B 3 10/23/2024 1255 1310 B 2 10/23/2024 1510 1530 C 3 10/23/2024 1330 1345 A 2 10/23/2024 1545 1650 A 1 10/24/2024 750 830 C 1 10/24/2024 910 930 B 3 10/24/2024 820 835 A 3 10/24/2024 850 920 A 1 10/24/2024 955 1020 B
5 Replies

# Re: Counting Time Periods

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

# Re: Counting Time Periods

@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 minutes

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

# Re: Counting Time Periods

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

# Re: Counting Time Periods

Yes! This works great! Thank you. You are the best!

# Re: Counting Time Periods

Glad to hear it! You're welcome :)
1 best response

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

# Re: Counting Time Periods

@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 minutes

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