Concurrent Tech Utilization

Doug Mutzig

Board Regular
Joined
Jan 1, 2019
Messages
52
Hi All!

I am trying to figure out a way to calculate staff utilization based on Start Time and Duration or Start and End Time (not sure which will give the better answer).

I have several columns of information in a sample workbook: Tech, Facility, Start (date & time), End (date&time), Start Time (time), End Time (time), Duration, Date.

I am trying to first figure out a count of concurrent activities happening per hour, and then per tech's duration how many other activites are happening. I was able to do a chart and then get rough numbers from that but there has to be a way to calculate this. I have 4 different tries from googling and reading through forums with mixed results - Con Test 2 seems to be the closest but doesn't match on all items that I can tell.

My eventual goal is to find out how many concurrent techs in use per facility per day (possibly per hour). I would love to pivot this so that I can have a chart along with it and slicers to adjust (facility, date/time, etc.)

Any help would be much appreciated.



Example.xlsx
BCDEFGHIJKLM
1ArrayArray
2TechFacilityStartEndStart TimeEnd TimeDurationDateCon Test 1Con Test 2Con Test 3Con Test 4
3A. MusedMaine Medical Center4/1/19 0:004/1/19 0:000:000:000:004/1/201911091
4Allie GraterKaiser Sunnybrook4/1/19 14:064/1/19 16:0014:0616:001:544/1/20192591
5Anne TeakCleveland Clinic Akron General4/1/19 7:544/1/19 12:157:5412:154:214/1/201921791
6Cherry BlossomCleveland Clinic Akron General4/1/19 9:194/1/19 10:499:1910:491:304/1/201931391
7Constance NoringCleveland Clinic Akron General4/1/19 9:524/1/19 13:249:5213:243:324/1/201941491
8Eileen SidewaysKaiser Sunnyside Medical Center4/1/19 11:014/1/19 12:2811:0112:281:274/1/20194991
9Ev R LastingParkview Medical Center4/1/19 6:054/1/19 13:156:0513:157:104/1/201961491
10Fay DawayMercy Medical Center4/1/19 0:004/1/19 0:000:000:000:004/1/20191991
11Frank N. SteinCrystal Clinic Orthopaedic Center4/1/19 0:004/1/19 0:000:000:000:004/1/20191891
12Hank R CheefMercyOne Waterloo Medical Center4/1/19 7:324/1/19 9:137:329:131:414/1/20194791
13Hugo FirstCrystal Clinic Orthopaedic Center4/1/19 0:004/1/19 0:000:000:000:004/1/20191791
14Isabelle RingingMercy Medical Center4/1/19 11:094/1/19 15:1311:0915:134:044/1/20197891
15Liz ErdCleveland Clinic Akron General4/1/19 0:004/1/19 0:000:000:000:004/1/20191691
16Maureen BiologistSt Vincent Medical Center - Ear4/1/19 10:474/1/19 13:0010:4713:002:134/1/20198891
17Olive TreeParkview Medical Center4/1/19 12:334/1/19 13:4912:3313:491:164/1/20196591
18P. Ann O'RecitalMethodist Le Bonheur Germantown Hospital4/1/19 0:004/1/19 0:000:000:000:004/1/20191591
19Paige TurnerMethodist Le Bonheur Germantown Hospital4/1/19 7:344/1/19 10:387:3410:383:044/1/20197891
20Percy VereMethodist Le Bonheur Germantown Hospital4/1/19 7:334/1/19 9:467:339:462:134/1/20197591
21Perry ScopeWhite Memorial Medical Center4/1/19 0:004/1/19 0:000:000:000:004/1/20191491
22Ray O'SunLe Bonheur Children's Medical Center4/1/19 7:254/1/19 10:227:2510:222:574/1/20199691
23Ray SinSumma Akron City4/1/19 0:004/1/19 0:000:000:000:004/1/20191391
24Rita BookMaine Medical Center4/1/19 0:004/1/19 0:000:000:000:004/1/20191291
25Rod KneeSt. Patrick Hospital4/1/19 10:164/1/19 11:1610:1611:161:004/1/201911691
26Rose BushSt. Patrick Hospital4/1/19 7:164/1/19 15:027:1615:027:464/1/201916591
27Roy L CommishunSt. Patrick Hospital4/1/19 10:474/1/19 13:4310:4713:432:564/1/201912491
28Teri DactylMaine Medical Center4/1/19 10:114/1/19 14:1810:1114:184:074/1/201916391
29UnknownKaiser Sunnyside Medical Center4/1/19 8:104/1/19 10:598:1010:592:494/1/201915291
Sheet2 (3)
Cell Formulas
RangeFormula
F3:F29F3=NUMBERVALUE(TEXT([@Start],"h:mm"))
G3:G29G3=NUMBERVALUE(TEXT([@End],"h:mm"))
H3:H29H3=IF([@End]>=[@Start],[@End]-[@Start],[@End]+1-[@Start])
I3:I29I3=NUMBERVALUE(TEXT([@Start],"mm/dd/yyyy"))
J3:J29J3=COUNTIFS(D$2:D3,"<"&E3,E$2:E3,">"&D3)+1
K3:K29K3=COUNTIFS(F3:F$29,"<="&G3,G3:G$29,">="&F3)+1
L3:L29L3=SUM(IF(FREQUENCY(IF(F$3:F29<=F3,IF(G$3:G29>=F3,MATCH(B$3:B$29,B$3:B$29,0))),ROW(B$3:B$29)-ROW(B$3)+1),1))
M3:M29M3=MAX(MAX(COUNTIFS(T_Data245[@[Start Time]],"<="&T_Data245[@[Start Time]],T_Data245[@[End Time]],">="&T_Data245[@[Start Time]])),MAX(COUNTIFS(T_Data245[@[Start Time]],"<="&T_Data245[@[End Time]],T_Data245[@[End Time]],">="&T_Data245[@[End Time]])))
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E3:E74Expression=E3<D3textNO
 

Attachments

  • Duration.png
    Duration.png
    106.9 KB · Views: 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,250
Members
416,963
Latest member
samfuge

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
Top