Extracting Data from Specific Data/Time range

[Engineer1998]

Hey guys, I have been trying to figure out how to take specific events and extract them during a specific date and time range. For example, if I want to sum the data a of specific event that happened from 7/17/2022 @ 6:00 AM to 7/17/2022 @ 6:00 PM. I would like to be able to just enter the date and time range I need and then excel tallies all those events in that time frame and extracts it from the event log. Like if i want to see how much time we spent on Pre_Job during 7/15/2022 @ 6:00 AM to 7/15/2022 @ 6:00 PM. I would like it to add up the total hours during that range.
Any help with this would be appreciated.

Start Date: 7/15/2022 Start Time: 6:00 AM End Date 7/15/2022 End Time: 6:00 PM

Pre Job: X hours During Job: X hours Open Well X hours ....ETC.

 

[jdellasala]

Create a table for your Start and End Times.

Book1
	A	B	C	D
1	Filter
2	Start Date	Start Time	End Date	End Time
3	07/08/2022	07:00:00 AM	07/16/2022	06:00:00 PM
Sheet1

That's a Table named Filter. Pull it into Power Query and fix the Data Types.

let
    Source = Excel.CurrentWorkbook(){[Name="Filter"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"Start Time", type time}, {"End Date", type date}, {"End Time", type time}})
in
    ChangedType

Load Filter ONLY as a Connection.
Now assume this is your data in a Table named Data. (clever naming scheme, hey?)

Book1
	F	G
1	Data
2	Date	Time
3	07/02/2022	11:34:43 AM
4	07/02/2022	10:41:14 PM
5	07/03/2022	02:20:48 PM
6	07/03/2022	05:15:15 PM
7	07/04/2022	03:00:41 PM
8	07/08/2022	01:23:08 AM
9	07/08/2022	06:20:14 AM
10	07/08/2022	07:11:13 AM
11	07/10/2022	08:58:49 PM
12	07/12/2022	12:36:15 AM
13	07/13/2022	11:36:36 PM
14	07/14/2022	02:54:51 AM
15	07/14/2022	12:54:08 PM
16	07/16/2022	08:13:44 AM
17	07/17/2022	02:47:31 AM
18	07/17/2022	01:18:56 PM
19	07/18/2022	07:36:55 PM
20	07/21/2022	12:16:39 PM
21	07/21/2022	04:01:42 PM
22	07/21/2022	05:55:10 PM
23	07/23/2022	03:05:58 AM
24	07/23/2022	10:59:21 AM
25	07/24/2022	05:28:12 PM
26	07/24/2022	10:31:23 PM
27	07/25/2022	09:12:01 PM
28	07/28/2022	01:18:16 AM
29	07/28/2022	02:22:57 PM
30	07/29/2022	10:36:33 PM
31	07/30/2022	03:16:54 PM
32	07/30/2022	04:44:33 PM
Sheet1

Pull that into Power Query. Rename the Query FilteredData and transform it like this.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}}),
    FilteredDate = Table.SelectRows(ChangedType, each [Date] >= Filter[Start Date]{0} and [Date] <= Filter[End Date]{0} ),
    FilteredTime = Table.SelectRows(FilteredDate, each [Time] >= Filter[Start Time]{0} and [Time] <= Filter[End Time]{0})
in
    FilteredTime

And load the table to a Worksheet.

Book1
	I	J
1	FilteredData
2	Date	Time
3	07/08/2022	07:11:13 AM
4	07/14/2022	12:54:08 PM
5	07/16/2022	08:13:44 AM
Sheet1

Now, whatever Start and End values you enter into the Filter table will be used on the next Refresh All to update the FilteredData table.

Sorry I didn't use your Start and End info, and would have used your table if it had been entered using XL2BB, but I think you'll get it, and it's a simpler to grasp.