Concurrent situations

G

Gcook90

New Member
Joined
Feb 3, 2015
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Hi all,

Working on a spreadsheet which shows telephone calls:

F column is Start time of the call (e.g. 21/05/2024 12:30)
J column is End time of the call (e.g. 21/05/2024 12:35)
K column is duration of the call in minutes and seconds (e.g. 5m 20s)

Is there a way to work out how many calls took place concurrently at the same time as each call based on this data?

Thanks in advance!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Gcook90 said:
Hi all,

Working on a spreadsheet which shows telephone calls:

F column is Start time of the call (e.g. 21/05/2024 12:30)
J column is End time of the call (e.g. 21/05/2024 12:35)
K column is duration of the call in minutes and seconds (e.g. 5m 20s)

Is there a way to work out how many calls took place concurrently at the same time as each call based on this data?

Thanks in advance!
Click to expand...
Can you share a sample of your data? THe xl2bb add in (link below) is a great tool that really supercharges you helping the forum help you. If you cannot use that, then please post your sample data as a table.

To answer your question, yes, there probably is a way.

Thanks in advance.
 
Upvote 0
awoohaw said:
Can you share a sample of your data? THe xl2bb add in (link below) is a great tool that really supercharges you helping the forum help you. If you cannot use that, then please post your sample data as a table.

To answer your question, yes, there probably is a way.

Thanks in advance.
Click to expand...
Example below.
Thank you

StartEndDuration
24/04/2024 00:14​
24/04/2024 00:14​
30s
24/04/2024 02:10​
24/04/2024 02:10​
22s
24/04/2024 03:19​
24/04/2024 03:19​
13s
24/04/2024 04:18​
24/04/2024 04:18​
8s
24/04/2024 06:23​
24/04/2024 06:23​
5s
24/04/2024 06:47​
24/04/2024 06:47​
10s
24/04/2024 06:50​
24/04/2024 06:50​
19s
24/04/2024 07:01​
24/04/2024 07:01​
10s
24/04/2024 07:04​
24/04/2024 07:05​
19s
24/04/2024 07:22​
24/04/2024 07:22​
17s
24/04/2024 07:38​
24/04/2024 07:39​
33s
24/04/2024 07:52​
24/04/2024 07:52​
4s
24/04/2024 07:57​
24/04/2024 07:58​
19s
24/04/2024 07:58​
24/04/2024 07:58​
14s
24/04/2024 08:12​
24/04/2024 08:12​
4s
24/04/2024 08:16​
24/04/2024 08:17​
52s
24/04/2024 08:17​
24/04/2024 08:29​
11m 25s
24/04/2024 08:25​
24/04/2024 08:26​
15s
24/04/2024 08:27​
24/04/2024 08:27​
10s
24/04/2024 08:29​
24/04/2024 08:29​
18s
24/04/2024 08:30​
24/04/2024 08:30​
24/04/2024 08:31​
24/04/2024 08:37​
6m 9s
24/04/2024 08:33​
24/04/2024 08:33​
19s
24/04/2024 08:36​
24/04/2024 08:51​
15m
24/04/2024 08:37​
24/04/2024 08:39​
1m 4s
 
Upvote 0
It looks like the seconds were left off, which makes the data a bit harder to work with. Could the format be changed to show the seconds, too?
 
Upvote 0
What version of excel are you using? Please update your profile so the version is displayed on your button and the forum can provide you with the best solution.
 
Upvote 0
How about
Fluff.xlsm
ABCD
1StartEndDuration
224/04/2024 00:1424/04/2024 00:1430s1
324/04/2024 02:1024/04/2024 02:1022s1
424/04/2024 03:1924/04/2024 03:1913s1
524/04/2024 04:1824/04/2024 04:188s1
624/04/2024 06:2324/04/2024 06:235s1
724/04/2024 06:4724/04/2024 06:4710s1
824/04/2024 06:5024/04/2024 06:5019s1
924/04/2024 07:0124/04/2024 07:0110s1
1024/04/2024 07:0424/04/2024 07:0519s1
1124/04/2024 07:2224/04/2024 07:2217s1
1224/04/2024 07:3824/04/2024 07:3933s1
1324/04/2024 07:5224/04/2024 07:524s1
1424/04/2024 07:5724/04/2024 07:5819s2
1524/04/2024 07:5824/04/2024 07:5814s2
1624/04/2024 08:1224/04/2024 08:124s1
1724/04/2024 08:1624/04/2024 08:1752s2
1824/04/2024 08:1724/04/2024 08:2911m 25s5
1924/04/2024 08:2524/04/2024 08:2615s2
2024/04/2024 08:2724/04/2024 08:2710s2
2124/04/2024 08:2924/04/2024 08:2918s2
2224/04/2024 08:3024/04/2024 08:301
2324/04/2024 08:3124/04/2024 08:376m 9s4
2424/04/2024 08:3324/04/2024 08:3319s2
2524/04/2024 08:3624/04/2024 08:5115m3
2624/04/2024 08:3724/04/2024 08:391m 4s3
Data
Cell Formulas
RangeFormula
D2:D26D2=COUNTIFS(B:B,">="&A2,A:A,"<="&B2)
 
Upvote 0
Hi,

Thanks for your replies, really appreciate it.

@Fluff The above isn't quite right. I'm probably explaining what I'm looking for badly. That shows the amount of calls each call shares a time window with - but what I'm looking for it the maximum amount of concurrent calls that any call has (the amount of simultaneous calls that are occurring).

@awoohaw I'm using Windows 365.
Good point @Vogateer have included them now.

StartEndDuration
24/04/2024 00:14:07​
24/04/2024 00:14:38​
30s
24/04/2024 02:10:34​
24/04/2024 02:10:57​
22s
24/04/2024 03:19:18​
24/04/2024 03:19:33​
13s
24/04/2024 04:18:35​
24/04/2024 04:18:45​
8s
24/04/2024 06:23:53​
24/04/2024 06:23:59​
5s
24/04/2024 06:47:21​
24/04/2024 06:47:32​
10s
24/04/2024 06:50:24​
24/04/2024 06:50:44​
19s
24/04/2024 07:01:10​
24/04/2024 07:01:21​
10s
24/04/2024 07:04:58​
24/04/2024 07:05:18​
19s
24/04/2024 07:22:29​
24/04/2024 07:22:47​
17s
24/04/2024 07:38:48​
24/04/2024 07:39:22​
33s
24/04/2024 07:52:35​
24/04/2024 07:52:41​
4s
24/04/2024 07:57:53​
24/04/2024 07:58:13​
19s
24/04/2024 07:58:18​
24/04/2024 07:58:33​
14s
24/04/2024 08:12:09​
24/04/2024 08:12:14​
4s
24/04/2024 08:16:27​
24/04/2024 08:17:20​
52s
24/04/2024 08:17:36​
24/04/2024 08:29:02​
11m 25s
24/04/2024 08:25:52​
24/04/2024 08:26:08​
15s
24/04/2024 08:27:11​
24/04/2024 08:27:22​
10s
24/04/2024 08:29:35​
24/04/2024 08:29:54​
18s
24/04/2024 08:30:52​
24/04/2024 08:30:54​
24/04/2024 08:31:06​
24/04/2024 08:37:47​
6m 9s
24/04/2024 08:33:33​
24/04/2024 08:33:53​
19s
24/04/2024 08:36:54​
24/04/2024 08:51:55​
15m
24/04/2024 08:37:55​
24/04/2024 08:39:00​
1m 4s
24/04/2024 08:40:07​
24/04/2024 08:58:59​
18m 51s
24/04/2024 08:40:22​
24/04/2024 08:40:30​
7s
24/04/2024 08:41:43​
24/04/2024 08:42:48​
1m 4s
24/04/2024 08:43:49​
24/04/2024 08:44:09​
24/04/2024 08:44:05​
24/04/2024 08:44:09​
3s
24/04/2024 08:44:16​
24/04/2024 08:44:48​
31s
24/04/2024 08:45:08​
24/04/2024 08:45:15​
6s
24/04/2024 08:47:09​
24/04/2024 08:47:29​
19s
24/04/2024 08:47:19​
24/04/2024 08:47:24​
4s
24/04/2024 08:47:37​
24/04/2024 09:05:12​
17m 35s
24/04/2024 08:50:09​
24/04/2024 08:50:30​
21s
 
Upvote 0
Gcook90 said:
I'm using Windows 365.
Click to expand...

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Gcook90 said:
what I'm looking for it the maximum amount of concurrent calls that any call has (the amount of simultaneous calls that are occurring).
Click to expand...
Can you post your expected results.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D
S
  • Solved
Formula to split date and time and work out energy usage trend
Replies
6
Views
460
Sundance_Kid
S
E
  • Question Question
Pulling together data from multiple MS Forms spreadsheets and displaying into a single league table
Replies
6
Views
329
excelnewbie999
E
A
  • Solved
add formulas in last column after sorting for each duplicates ID
Replies
9
Views
126
Peter_SSs
Peter_SSs
J
  • Solved
Sorting a Lot of data into multiple sheets (up to 200k lines)
Replies
4
Views
335
JMWh1t3
J

Forum statistics

Threads
1,223,910
Messages
6,175,316
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top