Matching info in two sheets with time stamps

[cmorriswilliams]

Hi there!

I have two sheets in one workbook. Sheet 1 will have a clock in and clock out for Employees. Sheet 2 will have a specific time stamp for when an employee performed a specific task.

I basically want to flag a YES if the Employee ex: Brian Poynter had a time stamp between his shift hours on 1/1/22.

SHEET 1

Driver Name	Clock In Date	Clock In Time	Clock Out Date	Clock Out Time
ELD-Brian Poynter	1/1/2022	01:00 PM	1/1/2022	09:00 PM
Fifth W- Swing - Chris Johnson	1/1/2022	11:31 AM	1/1/2022	09:25 PM
Fifth W- Swing -John Harrington	1/1/2022	10:45 AM	1/1/2022	08:06 PM
RO Swing - Mauricio Gonzalez	1/2/2022	05:28 PM	1/2/2022	08:51 PM

SHEET 2

Driver Name	Date/Time Stamp
ELD-Brian Poynter	1/1/2022 2:41 PM​
ELD-Shari Banta	1/1/2022 2:48 AM​
*BP - Raj Singh	1/1/2022 4:23 PM​
*BP - Jay Morgan	1/1/2022 5:36 AM​
Raoul Bartsch	1/1/2022 5:51 PM​

 

[alansidman]

I loaded each tab into Power Query and merged the two (Left Outer Join on the Name). Then in the merged table, I filtered out the null results and then compared the Time Stamp to be between the clock in and clock out date

Here are the Mcode for each query

To transform sheet 1.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Clock In Date", type date}, {"Clock In Time", type time}}),
    #"Inserted Merged Date and Time" = Table.AddColumn(#"Changed Type", "Merged", each [Clock In Date] & [Clock In Time], type datetime),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Merged Date and Time",{{"Clock Out Date", type date}, {"Clock Out Time", type time}}),
    #"Inserted Merged Date and Time1" = Table.AddColumn(#"Changed Type1", "Merged.1", each [Clock Out Date] & [Clock Out Time], type datetime),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Date and Time1",{"Clock In Date", "Clock In Time", "Clock Out Date", "Clock Out Time"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Merged", "Clocked In"}, {"Merged.1", "Clocked Out"}})
in
    #"Renamed Columns"

To transform sheet2

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Time Stamp", type datetime}})
in
    #"Changed Type"

To Join the two tables and get results

let
    Source = Table.NestedJoin(Table1, {"Driver Name"}, Table2, {"Driver Name"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Date Time Stamp"}, {"Table2.Date Time Stamp"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table2", each ([Table2.Date Time Stamp] = #datetime(2022, 1, 1, 14, 41, 0))),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each if[Table2.Date Time Stamp]>[Clocked In] and [Table2.Date Time Stamp]<[Clocked Out] then "YES" else null)
in
    #"Added Custom"

 

[Anthony47]

In addition to what already suggested by @alansidman above, if your data are in Sheet1!A:E and in Sheet2!A:B you could try the following formula in Sheet1!F2

=IF(SUM((Sheet2!A2:A10=A2)*((Sheet2!B2:B10)>=(B2+C2))*((Sheet2!B2:B10)<=(D2+E2)))>0,"YES","")

Then F2 copy down

Bye