Allocate the Hours based on Interval covered

Airam

New Member
Joined
Jun 23, 2010
Messages
19
Hi Everyone!

Asking for your expertise on this. My raw file consists of total hours covered per specific instance of Status/aux state. I want to identify how many of these hours are allocated based on a 15 minute interval. Please see attached excel file for you guidance.
Distribute hours in an Interval.xlsx
ABCDEFGHIJKLMN
1RAW DATAOutcome
2NAMEStatusDateTimeStatusEndDateTimeStateDuration (milliseconds)State Duration (sec)DATETime StartTime EndInterval26-Jun27-Jun28-Jun
3John Doe06/26/2023 09:14Available6/26/2023 09:1534.2660:00:3406/26/202309:14:4809:15:22HoursHoursHours
4John Doe06/26/2023 09:15OnCall6/26/2023 09:582578.4980:42:5806/26/202309:15:2209:58:2109:00
5John Doe06/26/2023 09:58Available6/26/2023 10:12841.9130:14:0206/26/202309:58:2110:12:2309:15
6John Doe06/26/2023 10:12Ticket6/26/2023 10:18391.3720:06:3106/26/202310:12:2310:18:5409:45
7John Doe06/26/2023 10:18Break6/26/2023 10:26441.3150:07:2106/26/202310:18:5410:26:1510:00
8John Doe06/26/2023 10:26Outbound6/26/2023 10:269.6020:00:1006/26/202310:26:1510:26:2510:15
9John Doe06/26/2023 10:26OnCall6/26/2023 11:223384.4290:56:2406/26/202310:26:2511:22:4910:30
10John Doe06/26/2023 11:22Outbound6/26/2023 11:2345.0920:00:4506/26/202311:22:4911:23:3510:45
11John Doe06/26/2023 11:23Break6/26/2023 11:39949.9560:15:5006/26/202311:23:3511:39:2411:00
12John Doe06/26/2023 11:39Outbound6/26/2023 11:393.7060:00:0406/26/202311:39:2411:39:2811:15
13John Doe06/26/2023 11:39OnCall6/26/2023 13:165818.7331:36:5906/26/202311:39:2813:16:2711:30
14John Doe06/26/2023 13:16Outbound6/26/2023 13:1735.6620:00:3606/26/202313:16:2713:17:0311:45
15John Doe06/27/2023 09:55Available6/27/2023 09:5519.8090:00:2006/27/202309:55:2709:55:4712:00
16John Doe06/27/2023 09:55OnCall6/27/2023 10:533471.3990:57:5106/27/202309:55:4710:53:3812:15
17John Doe06/27/2023 10:53Available6/27/2023 10:535.6220:00:0606/27/202310:53:3810:53:4412:30
18John Doe06/27/2023 10:53Break6/27/2023 11:10990.6280:16:3106/27/202310:53:4411:10:1512:45
19John Doe06/27/2023 11:10Break6/27/2023 11:17426.0870:07:0606/27/202311:10:1511:17:2113:00
20John Doe06/27/2023 11:17Outbound6/27/2023 11:1718.0360:00:1806/27/202311:17:2111:17:3913:15
21John Doe06/27/2023 11:17OnCall6/27/2023 13:086634.7291:50:3506/27/202311:17:3913:08:1413:30
22John Doe06/27/2023 13:08Outbound6/27/2023 13:0821.4140:00:2106/27/202313:08:1413:08:3513:45
23John Doe06/27/2023 13:08Lunch6/27/2023 13:401914.7350:31:5506/27/202313:08:3513:40:3014:00
24John Doe06/27/2023 13:40Outbound6/27/2023 13:42113.7150:01:5406/27/202313:40:3013:42:2314:15
25John Doe06/27/2023 13:54Available6/27/2023 13:56107.1980:01:4706/27/202313:54:5313:56:4114:30
26John Doe06/27/2023 13:58Available6/27/2023 13:597.3670:00:0706/27/202313:58:5613:59:0314:45
27John Doe06/27/2023 13:59OnCall6/27/2023 14:362219.6180:37:0006/27/202313:59:0314:36:0315:00
28John Doe06/27/2023 14:36Coaching6/27/2023 14:3625.8520:00:2606/27/202314:36:0314:36:2915:15
29John Doe06/27/2023 14:36Meeting6/27/2023 14:551150.4790:19:1006/27/202314:36:2914:55:3915:30
30John Doe06/27/2023 14:55Outbound6/27/2023 14:557.5930:00:0806/27/202314:55:3914:55:4715:45
31John Doe06/27/2023 14:55OnCall6/27/2023 15:422801.0120:46:4106/27/202314:55:4715:42:2816:00
32John Doe06/27/2023 15:42Outbound6/27/2023 15:45209.2830:03:2906/27/202315:42:2815:45:5716:15
33John Doe06/27/2023 15:45Break6/27/2023 16:01921.680:15:2206/27/202315:45:5716:01:1916:30
34John Doe06/27/2023 16:01Outbound6/27/2023 16:0116.8620:00:1706/27/202316:01:1916:01:3616:45
35John Doe06/27/2023 16:01OnCall6/27/2023 17:305360.9891:29:2106/27/202316:01:3617:30:5717:00
36John Doe06/27/2023 17:30Outbound6/27/2023 17:3135.9140:00:3606/27/202317:30:5717:31:3317:15
37John Doe06/28/2023 14:00Available6/28/2023 14:0115.9880:00:1606/28/202314:00:5314:01:0917:30
38John Doe06/28/2023 14:01OnCall6/28/2023 15:375760.9931:36:0106/28/202314:01:0915:37:1017:45
39John Doe06/28/2023 15:37Available6/28/2023 15:3717.8650:00:1806/28/202315:37:1015:37:2818:00
40John Doe06/28/2023 15:37OnCall6/28/2023 15:561126.4810:18:4606/28/202315:37:2815:56:1418:15
41John Doe06/28/2023 15:56Available6/28/2023 15:5622.4960:00:2206/28/202315:56:1415:56:3718:30
42John Doe06/28/2023 15:56Ticket6/28/2023 16:02331.8020:05:3206/28/202315:56:3716:02:0918:45
43John Doe06/28/2023 16:02Outbound6/28/2023 16:0399.5430:01:4006/28/202316:02:0916:03:4819:00
44John Doe06/28/2023 16:03Break6/28/2023 16:19968.9960:16:0906/28/202316:03:4816:19:5719:15
45John Doe06/28/2023 16:19Break6/28/2023 16:27423.0180:07:0306/28/202316:19:5716:27:0019:30
46John Doe06/28/2023 16:27Outbound6/28/2023 16:272.1710:00:0206/28/202316:27:0016:27:0219:45
47John Doe06/28/2023 16:27OnCall6/28/2023 16:42935.9120:15:3606/28/202316:27:0216:42:3820:00
48John Doe06/28/2023 16:42Outbound6/28/2023 16:4213.0610:00:1306/28/202316:42:3816:42:5120:15
49John Doe06/28/2023 16:42Break6/28/2023 16:44125.9790:02:0606/28/202316:42:5116:44:5720:30
50John Doe06/28/2023 16:44Outbound6/28/2023 16:441.4260:00:0106/28/202316:44:5716:44:5920:45
51John Doe06/28/2023 16:44OnCall6/28/2023 17:483837.2781:03:5706/28/202316:44:5917:48:5621:00
52John Doe06/28/2023 17:48Outbound6/28/2023 17:4916.1760:00:1606/28/202317:48:5617:49:1221:15
53John Doe06/28/2023 17:49Lunch6/28/2023 18:191845.7630:30:4606/28/202317:49:1218:19:5821:30
54John Doe06/28/2023 18:19Outbound6/28/2023 18:2027.4930:00:2706/28/202318:19:5818:20:2521:45
55John Doe06/28/2023 18:20OnCall6/28/2023 19:223750.911:02:3106/28/202318:20:2519:22:5622:00
56John Doe06/28/2023 19:22Outbound6/28/2023 19:2482.4130:01:2206/28/202319:22:5619:24:1922:15
57John Doe06/28/2023 19:24OnCall6/28/2023 20:112841.8870:47:2206/28/202319:24:1920:11:4122:30
58John Doe06/28/2023 20:11Outbound6/28/2023 20:13107.180:01:4706/28/202320:11:4120:13:2822:45
59John Doe06/28/2023 20:13Break6/28/2023 20:29972.4310:16:1206/28/202320:13:2820:29:4023:00
60John Doe06/28/2023 20:29Outbound6/28/2023 20:291.820:00:0206/28/202320:29:4020:29:4223:15
61John Doe06/28/2023 20:29OnCall6/28/2023 22:427956.8012:12:3706/28/202320:29:4222:42:1923:30
62John Doe06/28/2023 22:42Outbound6/28/2023 22:4234.7960:00:3506/28/202322:42:1922:42:5423:45
Sheet1
Cell Formulas
RangeFormula
F3:F62F3=E3/86400
G3:G62G3=TEXT(B3,"Mm/Dd/Yyyy")
H3:H62H3=TEXT(B3,"Hh:mm:ss")
I3:I62I3=TEXT(D3,"Hh:mm:ss")
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
I took 15 minutes to look at your data. I can't find an easy way to summarize the data into 15 minute time slots using a formula without first dividing your raw data into the proper time slots.
 
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,270
Members
452,628
Latest member
dd2

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