• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Time and Attendance|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Time and Attendance|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryTime and Attendance
sp_PrintTopic sp_TopicIcon
Time and Attendance
Avatar
Corrie Hiepner
Durban, South Africa
Member
Members
Level 0
Forum Posts: 7
Member Since:
January 10, 2020
sp_UserOfflineSmall Offline
1
January 10, 2020 - 10:40 pm
sp_Permalink sp_Print

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.

sp_AnswersTopicSeeAnswer See Answer
Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
2
January 11, 2020 - 6:35 am
sp_Permalink sp_Print

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

Avatar
Chris Yap
Member
Members
Level 0
Forum Posts: 162
Member Since:
August 21, 2019
sp_UserOfflineSmall Offline
3
January 11, 2020 - 11:08 pm
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
Avatar
Corrie Hiepner
Durban, South Africa
Member
Members
Level 0
Forum Posts: 7
Member Since:
January 10, 2020
sp_UserOfflineSmall Offline
4
January 13, 2020 - 4:24 pm
sp_Permalink sp_Print sp_EditHistory

Hi Chris,

Thank you so much!  You make it look so easy 🙂

Much appreciated!

Avatar
Corrie Hiepner
Durban, South Africa
Member
Members
Level 0
Forum Posts: 7
Member Since:
January 10, 2020
sp_UserOfflineSmall Offline
5
June 3, 2020 - 9:09 pm
sp_Permalink sp_Print sp_EditHistory

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.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1510
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
6
June 4, 2020 - 2:58 pm
sp_Permalink sp_Print

Hi Corrie,

no file attached.

regards

Phil

Avatar
Corrie Hiepner
Durban, South Africa
Member
Members
Level 0
Forum Posts: 7
Member Since:
January 10, 2020
sp_UserOfflineSmall Offline
7
June 4, 2020 - 4:01 pm
sp_Permalink sp_Print

Hi Phil,

Apologies, please see attached.

Thank you

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
8
June 4, 2020 - 8:32 pm
sp_Permalink sp_Print sp_EditHistory

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:

let
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.

Avatar
Corrie Hiepner
Durban, South Africa
Member
Members
Level 0
Forum Posts: 7
Member Since:
January 10, 2020
sp_UserOfflineSmall Offline
9
June 11, 2020 - 6:33 pm
sp_Permalink sp_Print

Hi Catalin,

Your assistance is much appreciated.

I was able to apply your code to my dataset and it worked.

Thank you

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: David, yonatan zelig, Christopher Anderson
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
yashal minahil
Oluwadamilola Ogun
Yannik H
dectator mang
Francis Drouillard
Orlando Inocente
Jovitha Clemence
Maloxat Axmatovna
Ricardo Freitas
Marko Meglic
Forum Stats:
Groups: 3
Forums: 24
Topics: 6200
Posts: 27182

 

Member Stats:
Guest Posters: 49
Members: 31861
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.