Log In
Register
Home
January 10, 2020
Offline
Hi Mynda,
I just watched your video on Extracting Start and End Dates from a list and would like to know how to apply this in my situation where I have a list of employee numbers, the date and the time they clocked in or out using the bio-metric system. Unfortunately there is no specific Time In and Time Out column. All the data is in the same column which is making it very difficult for me. I need to extract the First Time In and the Last Time Out per employee no per date.
I've attached a file with an example of the data and the desired outcome.
Any advice/assistance will be greatly appreciated.
See Answer
Trusted Members
December 20, 2019
Offline
Hi Corrie
I have no idea about power query and i sure someone will respond, but you could do what you want with a pivot table if it helps
Purfleet
August 21, 2019
Offline
Hi Corrie
Attached is the Power Query solution
First you need to group by Employee No and Date, for Aggregation, one for Min and one for Max
Answers Post
January 10, 2020
Offline
Hi Chris,
Thank you so much! You make it look so easy 🙂
Much appreciated!
January 10, 2020
Offline
Hi, I posted this earlier this year, and @Chris Yap, was able to assist, but I have a further complication now, as a Night Shift has been added to the mix. Chris was able to show me how to determine the First Time In and the Last Time Out, but with night shift, this spans over 2 dates/days. Can anyone advise how to achieve the same result for night shift?
I've attached the original file, with Chris's solution and a representation of my desired outcome.
Once again, any advice on how to do this is greatly appreciated.
October 5, 2010
Offline
Hi Corrie,
no file attached.
regards
Phil
January 10, 2020
Offline
Hi Phil,
Apologies, please see attached.
Thank you
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Corrie,
Without proper data, there is no easy way, just workarounds, and there are chances to fail in specific scenarios.
You can try this query:
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee No", type text}, {"Date", type date}, {"Time", type time}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "New Date", each if [Time]<=#time(4,30,0) then Date.AddDays([Date],-1) else [Date]),
#"Grouped Rows1" = Table.Group(#"Added Custom", {"New Date"}, {{"Count", each _, type table [Employee No=text, Date=date, Time=time, Custom=date, Index=number]}}),
#"Added Custom2" = Table.AddColumn(#"Grouped Rows1", "Date-Time In", each Number.From(List.First([Count][Date]))+Number.From(List.First([Count][Time])), type datetime),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Date-Time Out", each Number.From(List.Last([Count][Date]))+Number.From(List.Last([Count][Time])),type datetime),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"Date-Time In", type datetime}, {"Date-Time Out", type datetime}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Count", "New Date"})
in
#"Removed Columns"
As you can see, the query converts the date for entries where time is below 4:30 AM, the date will be 1 day before entry Date, this new date will be used for grouping. The grouping must return all rows, will not be a min or max, a table with all entries for that date will be returned.
From this grouped table, we simply take the first and last entries.
1 Guest(s)
