Resolving complex scenarios with time attendance

Todor

Todor

New Member
Joined
Oct 14, 2014
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
Hi there,

I have a fairly complex scenario in Excel that was solved with a LOT of nested formulas. The time attendance is recorded in the biometrics machine which is then exported into a csv or xlsx format. Since the time attendance is checked bi-monthly, there are thousands of logs to be checked. Part of the challenge is that the log is recorded in a single line, which means: employee ID, employee name, date-time, type of log (check-in, check-out, overtime in, overtime out).

If this was done for daytime employees, it would be significantly easier. However, there are night-shift employees checking in after 10 P.M. and checking out the next morning. Here is the short sample of the log record.

175Pacia.Christien Jake12/11/2019 21:59:04Check In
175Pacia.Christien Jake12/12/2019 08:03:57Check Out
175Pacia.Christien Jake12/12/2019 21:56:44Check In
175Pacia.Christien Jake12/13/2019 08:15:32Check Out
175Pacia.Christien Jake12/16/2019 08:50:58Check In
175Pacia.Christien Jake12/16/2019 19:43:38Check Out
148Palmon.Adnand12/02/2019 07:57:16Check In
148Palmon.Adnand12/02/2019 19:07:20Check Out
148Palmon.Adnand12/03/2019 07:58:57Check In
148Palmon.Adnand12/03/2019 18:44:29Check Out

There are instances where the employee misses the check-in or check-out or is absent, as well as the public holiday or the weekend. I have solved all scenarios to get times of attendance of to display missed logs with this array formula:

=IFERROR(INDEX(Holiday[Holiday], MATCH(DATEVALUE(Attendance[[#Headers],[12/2/2019]]), Holiday[Date],0)), IFERROR(IF(TIMEVALUE(INDEX(Raw_Data_Nov[Time], MATCH(1, ("Check Out"=Raw_Data_Nov[AttendanceState])*([@[Employee ID]]=Raw_Data_Nov[EmpID])*(DATEVALUE(Attendance[[#Headers],[12/3/2019]])=DATEVALUE(Raw_Data_Nov[Time])), 0)))<0.08, INDEX(Raw_Data_Nov[Date], MATCH(1, ("Check Out"=Raw_Data_Nov[AttendanceState])*([@[Employee ID]]=Raw_Data_Nov[EmpID])*(DATEVALUE(Attendance[[#Headers],[12/3/2019]])=DATEVALUE(Raw_Data_Nov[Time])), 0))*24-INDEX(Raw_Data_Nov[Date], MATCH(1, ("Check In"=Raw_Data_Nov[AttendanceState])*([@[Employee ID]]=Raw_Data_Nov[EmpID])*(DATEVALUE(Attendance[[#Headers],[12/2/2019]])=DATEVALUE(Raw_Data_Nov[Time])), 0))*24, #N/A), IFERROR(IFS(WEEKDAY(Attendance[[#Headers],[12/2/2019]],2)>6, "Sunday", AND(WEEKDAY(Attendance[[#Headers],[12/2/2019]],2)>5, ISERROR(INDEX(Raw_Data_Nov[Date], MATCH(1, ("Check In"=Raw_Data_Nov[AttendanceState])*([@[Employee ID]]=Raw_Data_Nov[EmpID])*(DATEVALUE(Attendance[[#Headers],[12/2/2019]])=DATEVALUE(Raw_Data_Nov[Time])), 0)))), "Saturday", AND(ISERROR(INDEX(Raw_Data_Nov[Date], MATCH(1, ("Check Out"=Raw_Data_Nov[AttendanceState])*([@[Employee ID]]=Raw_Data_Nov[EmpID])*(DATEVALUE(Attendance[[#Headers],[12/2/2019]])=DATEVALUE(Raw_Data_Nov[Time])), 0))), ISERROR(INDEX(Raw_Data_Nov[Date], MATCH(1, ("Check In"=Raw_Data_Nov[AttendanceState])*([@[Employee ID]]=Raw_Data_Nov[EmpID])*(DATEVALUE(Attendance[[#Headers],[12/2/2019]])=DATEVALUE(Raw_Data_Nov[Time])), 0)))), "Absent", ISERROR(INDEX(Raw_Data_Nov[Date], MATCH(1, ("Check In"=Raw_Data_Nov[AttendanceState])*([@[Employee ID]]=Raw_Data_Nov[EmpID])*(DATEVALUE(Attendance[[#Headers],[12/2/2019]])=DATEVALUE(Raw_Data_Nov[Time])), 0))), "No Check In", TIMEVALUE(INDEX(Raw_Data_Nov[Time], MATCH(1, ("Check In"=Raw_Data_Nov[AttendanceState])*([@[Employee ID]]=Raw_Data_Nov[EmpID])*(DATEVALUE(Attendance[[#Headers],[12/2/2019]])=DATEVALUE(Raw_Data_Nov[Time])), 0)))>0.75, INDEX(Raw_Data_Nov[Date], MATCH(1, ("Check Out"=Raw_Data_Nov[AttendanceState])*([@[Employee ID]]=Raw_Data_Nov[EmpID])*(DATEVALUE(Attendance[[#Headers],[12/3/2019]])=DATEVALUE(Raw_Data_Nov[Time])), 0))*24-INDEX(Raw_Data_Nov[Date], MATCH(1, ("Check In"=Raw_Data_Nov[AttendanceState])*([@[Employee ID]]=Raw_Data_Nov[EmpID])*(DATEVALUE(Attendance[[#Headers],[12/2/2019]])=DATEVALUE(Raw_Data_Nov[Time])), 0))*24, AND(ISERROR(INDEX(Raw_Data_Nov[Date], MATCH(1, ("Check Out"=Raw_Data_Nov[AttendanceState])*([@[Employee ID]]=Raw_Data_Nov[EmpID])*(DATEVALUE(Attendance[[#Headers],[12/2/2019]])=DATEVALUE(Raw_Data_Nov[Time])), 0))), ISERROR(INDEX(Raw_Data_Nov[Date], MATCH(1, ("Overtime Out"=Raw_Data_Nov[AttendanceState])*([@[Employee ID]]=Raw_Data_Nov[EmpID])*(DATEVALUE(Attendance[[#Headers],[12/2/2019]])=DATEVALUE(Raw_Data_Nov[Time])), 0)))), "No Check Out"), IFERROR(INDEX(Raw_Data_Nov[Date], SMALL(IF(Raw_Data_Nov[AttendanceState]="Check Out", IF(Raw_Data_Nov[EmpID]=[@[Employee ID]], IF(DATEVALUE(Raw_Data_Nov[Time])=DATEVALUE(Attendance[[#Headers],[12/2/2019]]), ROW(Raw_Data_Nov[Time])-ROW(INDEX(Raw_Data_Nov[Time],1,1))+1))),2))*24-INDEX(Raw_Data_Nov[Date], MATCH(1, ("Check In"=Raw_Data_Nov[AttendanceState])*([@[Employee ID]]=Raw_Data_Nov[EmpID])*(DATEVALUE(Attendance[[#Headers],[12/2/2019]])=DATEVALUE(Raw_Data_Nov[Time])), 0))*24, IFERROR(IF(TIMEVALUE(INDEX(Raw_Data_Nov[Time], MATCH(1, ("Check Out"=Raw_Data_Nov[AttendanceState])*([@[Employee ID]]=Raw_Data_Nov[EmpID])*(DATEVALUE(Attendance[[#Headers],[12/3/2019]])=DATEVALUE(Raw_Data_Nov[Time])), 0)))<0.08, INDEX(Raw_Data_Nov[Date], MATCH(1, ("Overtime Out"=Raw_Data_Nov[AttendanceState])*([@[Employee ID]]=Raw_Data_Nov[EmpID])*(DATEVALUE(Attendance[[#Headers],[12/3/2019]])=DATEVALUE(Raw_Data_Nov[Time])), 0))*24-INDEX(Raw_Data_Nov[Date], MATCH(1, ("Check In"=Raw_Data_Nov[AttendanceState])*([@[Employee ID]]=Raw_Data_Nov[EmpID])*(DATEVALUE(Attendance[[#Headers],[12/2/2019]])=DATEVALUE(Raw_Data_Nov[Time])), 0))*24, INDEX(Raw_Data_Nov[Date], MATCH(1, ("Overtime Out"=Raw_Data_Nov[AttendanceState])*([@[Employee ID]]=Raw_Data_Nov[EmpID])*(DATEVALUE(Attendance[[#Headers],[12/2/2019]])=DATEVALUE(Raw_Data_Nov[Time])), 0))*24-INDEX(Raw_Data_Nov[Date], MATCH(1, ("Check In"=Raw_Data_Nov[AttendanceState])*([@[Employee ID]]=Raw_Data_Nov[EmpID])*(DATEVALUE(Attendance[[#Headers],[12/2/2019]])=DATEVALUE(Raw_Data_Nov[Time])), 0))*24), INDEX(Raw_Data_Nov[Date], MATCH(1, ("Check Out"=Raw_Data_Nov[AttendanceState])*([@[Employee ID]]=Raw_Data_Nov[EmpID])*(DATEVALUE(Attendance[[#Headers],[12/2/2019]])=DATEVALUE(Raw_Data_Nov[Time])), 0))*24-INDEX(Raw_Data_Nov[Date], MATCH(1, ("Check In"=Raw_Data_Nov[AttendanceState])*([@[Employee ID]]=Raw_Data_Nov[EmpID])*(DATEVALUE(Attendance[[#Headers],[12/2/2019]])=DATEVALUE(Raw_Data_Nov[Time])), 0))*24)))))

The data is processed in the dynamic table with dates as headers to give part of the criteria, which is why I had to use the DATEVALUE function.

On thousands of rows of logs and 15 days within the data processing table, with hundreds of employees, formulas take a good amount of time to calculate and recalculate when things change.

I've been using DAX to solve much easier scenarios with much more data, but I can't figure it out for this.

Is there a way to speed things up with DAX for this scenario?

Any help is highly appreciated.

Todor
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
in the given sample where and what is the answer that you want.
 
Upvote 0
I am looking into getting a pivot table that can provide lates and undertime, as well as if someone missed the check-in or out, that looks like the current table I use:


Employee IDEmployee Name12/1/201912/2/201912/3/201912/4/201912/5/201912/6/2019
173Ong.John RaphaelSunday
0.06​
0.20​
0.19​
037Ong.Mio MarloSunday
0.47​
No Check In
0.16​
0.06​

Lates and undertime can be separate tables.
 
Upvote 0
Todor said:
I am looking into getting a pivot table that can provide lates and undertime, as well as if someone missed the check-in or out, that looks like the current table I use:


Employee IDEmployee Name12/1/201912/2/201912/3/201912/4/201912/5/201912/6/2019
173Ong.John RaphaelSunday
0.06​
0.20​
0.19​
037Ong.Mio MarloSunday
0.47​
No Check In
0.16​
0.06​

Lates and undertime can be separate tables.
Click to expand...
Assuming on your requirement i have made an excel file . Please share your email address so that i can forward.
What this file will do

You only need to dump data in Column A , B , C . Column D , E, F has formula that will simplify the date for further working
Column I till Column AN has date from where data will extract automatically upon dump .
I have used conditional formatting as well for easy identification . The rest i am sure you will figure it out . Sometimes you need to shape the raw data as per your requirement so that same can be used for future references.


System ReportSystem ReportSystem ReportKeysDate ExtractionTime Extraction
Employee IDNameReport Date & TimeDateTimeUnique Value01-Dec02-Dec03-Dec04-Dec05-Dec06-Dec07-Dec08-Dec09-Dec10-Dec11-Dec12-Dec13-Dec14-Dec15-Dec16-Dec17-Dec18-Dec19-Dec20-Dec21-Dec22-Dec23-Dec24-Dec25-Dec26-Dec27-Dec28-Dec29-Dec30-DecSummary
175Pacia.Christien Jake12-11-2019 21:59Pacia.Christien Jake4381011-Dec-20199:59:04 PMCheck InEmployee IDEmployee Name123456789101112131415161718192021222324252627282930Working Days
175Pacia.Christien Jake12-12-2019 8:03Pacia.Christien Jake4381112-Dec-20198:03:57 AMCheck Out175Pacia.Christien JakeSundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check In9:59:04 PM8:03:57 AM8:15:32 AMSaturdaySunday8:50:58 AMNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check In4
175Pacia.Christien Jake12-12-2019 21:56Pacia.Christien Jake4381112-Dec-20199:56:44 PMCheck In148Palmon.AdnandSunday7:57:16 AM7:58:57 AMNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check In2
175Pacia.Christien Jake12/13/2019 08:15:32Pacia.Christien Jake4381213-Dec-20198:15:32 AMCheck Out--SundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check In0
175Pacia.Christien Jake12/16/2019 08:50:58Pacia.Christien Jake4381516-Dec-20198:50:58 AMCheck In--SundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check In0
175Pacia.Christien Jake12/16/2019 19:43:38Pacia.Christien Jake4381516-Dec-20197:43:38 PMCheck Out--SundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check In0
148Palmon.Adnand12-02-2019 7:57Palmon.Adnand4380102-Dec-20197:57:16 AMCheck In--SundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check In0
148Palmon.Adnand12-02-2019 19:07Palmon.Adnand4380102-Dec-20197:07:20 PMCheck Out--SundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check In0
148Palmon.Adnand12-03-2019 7:58Palmon.Adnand4380203-Dec-20197:58:57 AMCheck In--SundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check In0
148Palmon.Adnand12-03-2019 18:44Palmon.Adnand4380203-Dec-20196:44:29 PMCheck Out--SundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check In0

In a nutshell , when you dump the data , the formula will extract unique employee and input their checking time for each date . Incase he / she doesnt login then no checking will appear . Saturday and Sunday will appear on table automatically and same will be highlighted automatically as per month .
If there is login time same will be highlighted . You will need to alter the things as per your requirement.

Lastly and most important . Please ensure that your system date format should appear in DD-MM-YYY instead of MM-DD-YYY for the formula to work correctly. Just go to pc Region setting and date and you will get the picture.

Hope this helps!
 
Upvote 0
This is interesting - there was a similar question on the PowerBI boards for someone looking to track parking lot in and out with a similar raw data structure - each event (in/out) was a separate row.

Your Excel formula is impressive and terrifying!

If your raw data isn't too large, check out this post about checking the next row in Power Query. What you can do is sort your data, as in your first example, so that each row is sorted by time and employee.

Then you can use the syntax in the linked post to "look ahead" to the next row. If it's a start event, is the next row a stop event for the same employee, and vice versa. A quick modification of the text (assuming your previous act was to add the index) could be to add a new column as follows

if [Event] = "Check In" then
if "#"Added Index"{[Index]+1} [Employee ID] = [Employee ID] and "#"Added Index"{[Index]+1} [Event] = "Check Out" then #"Added Index"{[Index]+1} [DateTime]
else if [Event] = "Check Out" then
etc
etc.

Once you have the correct in and out on the same line (if there's a match) you can then add another column to do your date arithmetic if both values are there, or indicate a missed punch if one is missing.
 
Upvote 0
earthworm said:
Assuming on your requirement i have made an excel file . Please share your email address so that i can forward.
What this file will do

You only need to dump data in Column A , B , C . Column D , E, F has formula that will simplify the date for further working
Column I till Column AN has date from where data will extract automatically upon dump .
I have used conditional formatting as well for easy identification . The rest i am sure you will figure it out . Sometimes you need to shape the raw data as per your requirement so that same can be used for future references.


System ReportSystem ReportSystem ReportKeysDate ExtractionTime Extraction
Employee IDNameReport Date & TimeDateTimeUnique Value01-Dec02-Dec03-Dec04-Dec05-Dec06-Dec07-Dec08-Dec09-Dec10-Dec11-Dec12-Dec13-Dec14-Dec15-Dec16-Dec17-Dec18-Dec19-Dec20-Dec21-Dec22-Dec23-Dec24-Dec25-Dec26-Dec27-Dec28-Dec29-Dec30-DecSummary
175Pacia.Christien Jake12-11-2019 21:59Pacia.Christien Jake4381011-Dec-20199:59:04 PMCheck InEmployee IDEmployee Name123456789101112131415161718192021222324252627282930Working Days
175Pacia.Christien Jake12-12-2019 8:03Pacia.Christien Jake4381112-Dec-20198:03:57 AMCheck Out175Pacia.Christien JakeSundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check In9:59:04 PM8:03:57 AM8:15:32 AMSaturdaySunday8:50:58 AMNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check In4
175Pacia.Christien Jake12-12-2019 21:56Pacia.Christien Jake4381112-Dec-20199:56:44 PMCheck In148Palmon.AdnandSunday7:57:16 AM7:58:57 AMNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check In2
175Pacia.Christien Jake12/13/2019 08:15:32Pacia.Christien Jake4381213-Dec-20198:15:32 AMCheck Out--SundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check In0
175Pacia.Christien Jake12/16/2019 08:50:58Pacia.Christien Jake4381516-Dec-20198:50:58 AMCheck In--SundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check In0
175Pacia.Christien Jake12/16/2019 19:43:38Pacia.Christien Jake4381516-Dec-20197:43:38 PMCheck Out--SundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check In0
148Palmon.Adnand12-02-2019 7:57Palmon.Adnand4380102-Dec-20197:57:16 AMCheck In--SundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check In0
148Palmon.Adnand12-02-2019 19:07Palmon.Adnand4380102-Dec-20197:07:20 PMCheck Out--SundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check In0
148Palmon.Adnand12-03-2019 7:58Palmon.Adnand4380203-Dec-20197:58:57 AMCheck In--SundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check In0
148Palmon.Adnand12-03-2019 18:44Palmon.Adnand4380203-Dec-20196:44:29 PMCheck Out--SundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check InNo Check InNo Check InNo Check InNo Check InSaturdaySundayNo Check In0

In a nutshell , when you dump the data , the formula will extract unique employee and input their checking time for each date . Incase he / she doesnt login then no checking will appear . Saturday and Sunday will appear on table automatically and same will be highlighted automatically as per month .
If there is login time same will be highlighted . You will need to alter the things as per your requirement.

Lastly and most important . Please ensure that your system date format should appear in DD-MM-YYY instead of MM-DD-YYY for the formula to work correctly. Just go to pc Region setting and date and you will get the picture.

Hope this helps!
Click to expand...

Thank you for taking the time to investigate better options for me to solve this situation. Unfortunately, it is still solving the scenario in Excel, which is what I'm trying to avoid. We have tens of thousands of rows per month for logs and it's becoming a challenge to analyze it in Excel.

That's why I was looking for a solution in DAX, which can handle large datasets with ease. Thank you again, for your efforts.
 
Upvote 0
macfuller said:
This is interesting - there was a similar question on the PowerBI boards for someone looking to track parking lot in and out with a similar raw data structure - each event (in/out) was a separate row.

Your Excel formula is impressive and terrifying!

If your raw data isn't too large, check out this post about checking the next row in Power Query. What you can do is sort your data, as in your first example, so that each row is sorted by time and employee.

Then you can use the syntax in the linked post to "look ahead" to the next row. If it's a start event, is the next row a stop event for the same employee, and vice versa. A quick modification of the text (assuming your previous act was to add the index) could be to add a new column as follows

if [Event] = "Check In" then
if "#"Added Index"{[Index]+1} [Employee ID] = [Employee ID] and "#"Added Index"{[Index]+1} [Event] = "Check Out" then #"Added Index"{[Index]+1} [DateTime]
else if [Event] = "Check Out" then
etc
etc.

Once you have the correct in and out on the same line (if there's a match) you can then add another column to do your date arithmetic if both values are there, or indicate a missed punch if one is missing.
Click to expand...

You're on to something! I am completely new to this type of syntax and haven't used Power Query more than a few times. After following your guidelines and the steps listed on the site you linked to, I've managed to get things going.

I spent some time looking for similar scenarios online as well, but they are not common.

This is how far I've got:

if [AttendanceState] = "Check In" then
if #"Added Index"{[Index]+1} [EmpID] = [EmpID] and #"Added Index"{[Index]+1} [AttendanceState] = "Check Out" or #"Added Index"{[Index]+1} [AttendanceState] = "Overtime Out" then
#"Added Index"{[Index]+1} [Time] else "No Check Out" else if [AttendanceState] = "Check Out" then if #"Added Index"{[Index]} [EmpID] = [EmpID] and #"Added Index"{[Index]} [AttendanceState] = "Check In" then "" else "No Check In" else "Error"

Screenshot (9).png


The challenge is my full understanding of the syntax. There are scenarios where I can't figure out how to write the formula correctly.

For example, if there 'event' is check-out, and there's a check-in in the previous row, then blank or zero, whichever one is recommended. It should be followed by if the event is check-out and there's no check-in in the previous row, then the result should be no check-in. There are other scenarios with overtime in and out, sometimes missing and sometimes half recorded.

I am almost there, but just need a nudge in the right direction. Your help has been outstanding! Such 'simplicity' with Power Query can save me so much time designing terrifying formulas.
 
Upvote 0
You've gotten pretty far for a rookie!

Your logic is going to be complicated, as it was in the Excel formula. I'd say take a step back before you go too much further and think about what your table looks like when you're done. Will the concept of having separate check in/check out rows disappear? What does a check out without a check in look like (or vice versa)? What about if you have a check in followed by a check out, but the employee missed the first check out and the next check in (e.g. checked in Friday morning, missed Friday check out and Monday check in, checked out Monday)? For the night shift people which day does the time apply to?

Will you need to keep separate rows for "orphans", rows that aren't preceded and followed by their opposite? There's a missed punch obviously, but do you need to credit them with some time? Work through the logic to figure out how many different values your new column will have. Duration calculations can follow once you have the rows properly matched.

Also, as you may have seen in the article, the last row throws an error in the new column because there's no +1 index. You can add logic to check a preceding row to what you're doing as well, so instead of your checkout row saying "No Check In" you can look back and see if there's a valid check in (within a normal shift time frame?) and mark the row for deletion. But, your first row of data will also throw an error in that case because there's no -1 index row.

You can use the try word to test for errors in M - try . This is getting a bit beyond my ability to help as I have only written very basic tests.

BackwardTest = try #"Added Index"{[Index]-1} [EmpID]
if BackwardTest[HasError] then ... else ...
ForwardTest = try #"Added Index"{[Index]+1} [EmpID]
etc.

But, how to shoehorn all that test logic into a single statement for the Add Column piece will require better help than I can offer! You could cycle through the data a couple of times, first create a column that tests for BOF and EOF, then another column with the primary logic to say what happens. That's a hit on performance obviously. Or you could cheat and just throw away the first and last rows ;).

Overall for your testing I'd suggest create as many additional columns as you need to make sure the logic is working to reach your final state. You can combine the forks at the end into a single beast statement as you did for the Excel formula. It's too bad there's no daxformatter.com equivalent for M, but use lots of line feeds and white space to comment your logic.

By the way, if performance suffers too much with multiple columns there's another option. It's another learning curve for you in managing functions, but this will automatically give you next or previous row values in your table. There's no logic so it's just going to pull the values you asked for, but you can save yourself the lookahead stuff and just work off the same line once it's in there. BOF and EOF will give null instead of errors, so that's easy enough to test for. So if you pull both previous and next check in values and the times, you're probably mostly on the way to getting what you need.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Power Pivot - Sum Issue
Replies
2
Views
401
d0hb0y
D
L
  • Question Question
Help win unpivot super slow query
2
Replies
15
Views
1K
labello27
L
G
  • Question Question
2 Table Running Total Not Working in Power BI
Replies
0
Views
474
GeeWhiz7
G
R
  • Question Question
RANKX on date by group
Replies
0
Views
1K
rpadminserver
R
ExcelAtEverything
  • Solved
Fill In Missing Dates in Power Query
Replies
2
Views
5K
ExcelAtEverything
ExcelAtEverything

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

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