Time Ranges, Total Time Overlapped

A

AudiMocz

New Member
Joined
Aug 2, 2021
Messages
3
Platform
  1. MacOS
Hello, I’m looking to create an easy-to-use log for my EMS department. I need to see how many times our ambulances are out at the same time on calls and for how long in 15 minute increments or on a comprehensive timeline. I have been provided with the format our local council is looking for, hence the over-complication. However, we really need the data to secure more funding for future resources.

I am looking for a way to create a formula, in military time, that would read for example:

When a time in this column is after 00:30:00 AND before 01:00:00, show “CALL” when true, if false show “STATION”.

I keep falling flat. I have a column of numbers that may not be completely filled, given a fluctuating call volume, leaving blanks or zeroes. To save my crew from stressing over formulas, I really need a plug and chug way to copy/paste our call log times and not redo the formula each day. We have the times ready and waiting, it's just moving them to this resource tracker.

Thank you so much for your help,

And sorry we can't use the xl2bb we can't upload anything to station computers.
 

Attachments

  • Screen Shot 2021-08-02 at 10.06.08 PM.png
    Screen Shot 2021-08-02 at 10.06.08 PM.png
    97.1 KB · Views: 13
  • Screen Shot 2021-08-02 at 10.24.23 PM.png
    Screen Shot 2021-08-02 at 10.24.23 PM.png
    220.7 KB · Views: 13

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Hi and welcome to MrExcel,

I've taken the liberty to change the call log sheet to this:
DispatchIn Service
Unit 1211:31:1711:35:00
Unit 1214:39:0015:22:00
Unit 1216:25:0017:10:00
Unit 1408:10:0008:15:00
Unit 1416:31:0017:59:13
Unit 1423:10:0000:47:00
Unit 2008:25:0009:09:37
Unit 2012:43:0016:46:42


This opens up the opportunity to add formula's to each row for time range determination, like this:
MrExcel - Time Ranges - Total Time Overlapped.xlsx
ABCDEFGH
1DispatchIn Service00:00:0000:15:0000:30:0000:45:0001:00:00
2Unit 1211:31:1711:35:00FALSEFALSEFALSEFALSEFALSE
3Unit 1214:39:0015:22:00FALSEFALSEFALSEFALSEFALSE
4Unit 1216:25:0017:10:00FALSEFALSEFALSEFALSEFALSE
5Unit 1408:10:0008:15:00FALSEFALSEFALSEFALSEFALSE
6Unit 1416:31:0017:59:13FALSEFALSEFALSEFALSEFALSE
7Unit 1423:10:0000:47:00TRUETRUETRUETRUEFALSE
8Unit 2008:25:0009:09:37FALSEFALSEFALSEFALSEFALSE
9Unit 2012:43:0016:46:42FALSEFALSEFALSEFALSEFALSE
Call Log
Cell Formulas
RangeFormula
E1:H1E1=D1+TIME(0,15,0)
D2:H9D2=(($B2<IF(($B2>$C2)*(FLOOR($B2,1/96)>D$1),E$1+1,E$1))*(IF(($B2>$C2),$C2+1,$C2)>IF(($B2>$C2)*(FLOOR($B2,1/96)>D$1),D$1+1,D$1))*(MIN(IF(($B2>$C2),$C2+1,$C2),IF(($B2>$C2)*(FLOOR($B2,1/96)>D$1),E$1+1,E$1))-MAX($B2,IF(($B2>$C2)*(FLOOR($B2,1/96)>D$1),D$1+1,D$1)))*(15/(IF(($B2>$C2),$C2+1,$C2)-$B2)))>0


The tracking sheet could look like this:
MrExcel - Time Ranges - Total Time Overlapped.xlsx
ABCDEFGHIJKLMNOPQR
100000100020003000400
201530450153045015304501530450
3Unit 12stationstationstationstationstationstationstationstationstationstationstationstationstationstationstationstationstation
4Unit 14CallCallCallCallstationstationstationstationstationstationstationstationstationstationstationstationstation
5Unit 16stationstationstationstationstationstationstationstationstationstationstationstationstationstationstationstationstation
6Unit 20stationstationstationstationstationstationstationstationstationstationstationstationstationstationstationstationstation
7
8Overlap11110000000000000
9
10Total Times at each level
11Green status915
12
13Yellow status405
14
15Red status90
16
17Black status30
Tracking
Cell Formulas
RangeFormula
F1,R1,N1,J1F1=TIME(HOUR(B1)+1,0,0)
B2,F2,J2,N2,R2B2=TIME(0,0,0)
C2,G2,K2,O2C2=TIME(0,15,0)
D2,H2,L2,P2D2=TIME(HOUR(D1),30,0)
E2,I2,M2,Q2E2=TIME(HOUR(E1),45,0)
B3:R6B3=IF(COUNTIFS('Call Log'!$A$2:$A$9,Tracking!$A3,'Call Log'!D$2:D$9,TRUE)>0,"Call","station")
B8:R8B8=COUNTIF(B3:B6,"Call")
B11B11=COUNTIF(B8:CS8,0)*15
B13B13=COUNTIF(B8:CS8,1)*15
B15B15=COUNTIF(B8:CS8,2)*15
B17B17=COUNTIF(B8:CS8,3)*15


If you want to use my test version, download it from here Box

Hope this helps.
 
Upvote 0
Solution
jorismoerings said:
Hi and welcome to MrExcel,

I've taken the liberty to change the call log sheet to this:
DispatchIn Service
Unit 1211:31:1711:35:00
Unit 1214:39:0015:22:00
Unit 1216:25:0017:10:00
Unit 1408:10:0008:15:00
Unit 1416:31:0017:59:13
Unit 1423:10:0000:47:00
Unit 2008:25:0009:09:37
Unit 2012:43:0016:46:42


This opens up the opportunity to add formula's to each row for time range determination, like this:
MrExcel - Time Ranges - Total Time Overlapped.xlsx
ABCDEFGH
1DispatchIn Service00:00:0000:15:0000:30:0000:45:0001:00:00
2Unit 1211:31:1711:35:00FALSEFALSEFALSEFALSEFALSE
3Unit 1214:39:0015:22:00FALSEFALSEFALSEFALSEFALSE
4Unit 1216:25:0017:10:00FALSEFALSEFALSEFALSEFALSE
5Unit 1408:10:0008:15:00FALSEFALSEFALSEFALSEFALSE
6Unit 1416:31:0017:59:13FALSEFALSEFALSEFALSEFALSE
7Unit 1423:10:0000:47:00TRUETRUETRUETRUEFALSE
8Unit 2008:25:0009:09:37FALSEFALSEFALSEFALSEFALSE
9Unit 2012:43:0016:46:42FALSEFALSEFALSEFALSEFALSE
Call Log
Cell Formulas
RangeFormula
E1:H1E1=D1+TIME(0,15,0)
D2:H9D2=(($B2<IF(($B2>$C2)*(FLOOR($B2,1/96)>D$1),E$1+1,E$1))*(IF(($B2>$C2),$C2+1,$C2)>IF(($B2>$C2)*(FLOOR($B2,1/96)>D$1),D$1+1,D$1))*(MIN(IF(($B2>$C2),$C2+1,$C2),IF(($B2>$C2)*(FLOOR($B2,1/96)>D$1),E$1+1,E$1))-MAX($B2,IF(($B2>$C2)*(FLOOR($B2,1/96)>D$1),D$1+1,D$1)))*(15/(IF(($B2>$C2),$C2+1,$C2)-$B2)))>0


The tracking sheet could look like this:
MrExcel - Time Ranges - Total Time Overlapped.xlsx
ABCDEFGHIJKLMNOPQR
100000100020003000400
201530450153045015304501530450
3Unit 12stationstationstationstationstationstationstationstationstationstationstationstationstationstationstationstationstation
4Unit 14CallCallCallCallstationstationstationstationstationstationstationstationstationstationstationstationstation
5Unit 16stationstationstationstationstationstationstationstationstationstationstationstationstationstationstationstationstation
6Unit 20stationstationstationstationstationstationstationstationstationstationstationstationstationstationstationstationstation
7
8Overlap11110000000000000
9
10Total Times at each level
11Green status915
12
13Yellow status405
14
15Red status90
16
17Black status30
Tracking
Cell Formulas
RangeFormula
F1,R1,N1,J1F1=TIME(HOUR(B1)+1,0,0)
B2,F2,J2,N2,R2B2=TIME(0,0,0)
C2,G2,K2,O2C2=TIME(0,15,0)
D2,H2,L2,P2D2=TIME(HOUR(D1),30,0)
E2,I2,M2,Q2E2=TIME(HOUR(E1),45,0)
B3:R6B3=IF(COUNTIFS('Call Log'!$A$2:$A$9,Tracking!$A3,'Call Log'!D$2:D$9,TRUE)>0,"Call","station")
B8:R8B8=COUNTIF(B3:B6,"Call")
B11B11=COUNTIF(B8:CS8,0)*15
B13B13=COUNTIF(B8:CS8,1)*15
B15B15=COUNTIF(B8:CS8,2)*15
B17B17=COUNTIF(B8:CS8,3)*15


If you want to use my test version, download it from here Box

Hope this helps.
Click to expand...
This is working perfectly, only thing I can’t get figured out is turning the True/False to Station/On Call. Tracking keeps pulling up a different window?

Thank you so much.
 
Upvote 0
Hi,

AudiMocz said:
This is working perfectly, only thing I can’t get figured out is turning the True/False to Station/On Call. Tracking keeps pulling up a different window?
Click to expand...

To change the True/False status on the "Call log" sheet, you need to amend the formula in D2 to:
Excel Formula: 
=IF((($B2<IF(($B2>$C2)*(FLOOR($B2,1/96)>D$1),E$1+1,E$1))*(IF(($B2>$C2),$C2+1,$C2)>IF(($B2>$C2)*(FLOOR($B2,1/96)>D$1),D$1+1,D$1))*(MIN(IF(($B2>$C2),$C2+1,$C2),IF(($B2>$C2)*(FLOOR($B2,1/96)>D$1),E$1+1,E$1))-MAX($B2,IF(($B2>$C2)*(FLOOR($B2,1/96)>D$1),D$1+1,D$1)))*(15/(IF(($B2>$C2),$C2+1,$C2)-$B2)))>0;"Call","Station"

But be aware, the tracking sheet relies on a COUNTIF function which you also need to amend to make sure the tracking sheet summarizes correctly.
The change on the tracking sheet in cell B3 would be:

Excel Formula: 
=IF(COUNTIFS('Call Log'!$A$2:$A$9,Tracking!$A3,'Call Log'!D$2:D$9,"Call")>0,"Call","station")
 
Upvote 0
Ok i got it! Last thing is the call log we’re provided with lists the date and time (07/25/2021 02:00:15), is there a way I can automate taking the time out? Or is there a work around I’m missing?
 
Upvote 0
Hi,

There's a way to take out the date of a date time field but be aware because this will mess up the status field because it does not factor in dates.
Just for explanation; assume you've got a 1 truck-roll per day, the dispatch time is the same each day and the assigned truck is different.
Your call log will look like this:
MrExcel - Time Ranges - Total Time Overlapped.xlsx
ABCDE
1DispatchIn ServiceDispatch wo DateIn Service wo date
2Unit 121-8-2021 11:311-8-2021 11:3511:31:1711:35:00
3Unit 142-8-2021 11:312-8-2021 11:3511:31:1711:35:00
4Unit 203-8-2021 11:313-8-2021 11:3511:31:1711:35:00
Call Log
Cell Formulas
RangeFormula
D2:E4D2=B2-INT(B2)


Looks fine because the model distributes the time in a correct over 24 Hrs however the tracking sheet does not take date into account hence creates a black status because the truck roll are overlapping in time.

00001100
015450153045
Unit 12stationstationstationstationstationCallstation
Unit 14stationstationstationstationstationCallstation
Unit 16stationstationstationstationstationstationstation
Unit 20stationstationstationstationstationCallstation
Overlap0000030
Total Times at each level
Green status1425
Yellow status0
Red status0
Black status15


This can be resolved by dividing the count of truckroll by the count of unique dates in the call log, like this:

MrExcel - Time Ranges - Total Time Overlapped.xlsx
ABCASATAUAVAWAX
1000011001200
20154501530450
3Unit 12stationstationstationstationstationCallstationstation
4Unit 14stationstationstationstationstationCallstationstation
5Unit 16stationstationstationstationstationstationstationstation
6Unit 20stationstationstationstationstationCallstationstation
7
8Overlap00000100
9
10
11Unique date in call log33
12
13Total Times at each level
14Green status1425
15
16Yellow status15
17
18Red status0
19
20Black status0
Tracking
Cell Formulas
RangeFormula
AT1,AX1AT1=TIME(HOUR(AP1)+1,0,0)
B2,AT2,AX2B2=TIME(0,0,0)
C2,AU2C2=TIME(0,15,0)
AS2,AW2AS2=TIME(HOUR(AS1),45,0)
AV2AV2=TIME(HOUR(AV1),30,0)
B3:C6,AS3:AX6B3=IF(COUNTIFS('Call Log'!$A$2:$A$4,Tracking!$A3,'Call Log'!F$2:F$4,"Call")>0,"Call","station")
B8:C8,AS8:AX8B8=COUNTIF(B3:B6,"Call")/$B$11
B11B11=COUNT(UNIQUE(INT('Call Log'!B2:B4),TRUE))
C11C11=SUMPRODUCT(1/COUNTIF('Call Log'!B2:B4,'Call Log'!B2:B4))
B14B14=COUNTIF(B8:CS8,0)*15
B16B16=COUNTIF(B8:CS8,1)*15
B18B18=COUNTIF(B8:CS8,2)*15
B20B20=COUNTIF(B8:CS8,3)*15
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B8:CS8Expression=(COUNTIF(B3:B6;"Call")/$B$11)=3textNO
B8:CS8Expression=(COUNTIF(B3:B6;"Call")/$B$11)=2textNO
B8:CS8Expression=(COUNTIF(B3:B6;"Call")/$B$11)=1textNO
B8:CS8Expression=(COUNTIF(B3:B6;"Call")/$B$11)=0textNO


Just as a note: depending on the excel version there are 2 ways to create a count of unique dates. The formula in Cell B11 works with Office 365, the formula in C11 works on the the other excel versions.
Be aware, it's not the perfect solution but with the given format..

Hopes this helps.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Assistance with intelligently adding up times in a time sheet where some times overlap
Replies
2
Views
301
NHSChris
N
S
  • Question
Automatic accrual calculations by day with accrual rate?
Replies
0
Views
240
Sunshine8790
S
A
  • Solved
Comparing two excel files for matching / mismatching rows (long text strings - match() wont work
Replies
4
Views
555
ahaye
A
T
  • Question
Call TekXL Screen Capture from user-defined macro?
Replies
1
Views
514
TheWaterbug
T
E
  • Question
Conditional Formatting, Dates and Times
Replies
6
Views
654
etaf
etaf

Forum statistics

Threads
1,214,929
Messages
6,122,315
Members
449,081
Latest member
tanurai

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