Help needed

%3CLINGO-SUB%20id%3D%22lingo-sub-2052557%22%20slang%3D%22en-US%22%3EHelp%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2052557%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20attendance%20file%20with%20our%20employees'%20schedules%20and%20their%20login%20and%20logout%20record.%20The%20problem%20is%2C%20they%20have%20multiple%20logins%2Flogouts.%20What%20I%20want%20to%20do%20is%20get%20the%20first%20login%20and%20the%20last%20logout%20of%20each%20employee%2C%20and%20a%20remark%20of%20%22tardy%22%20if%20they%20log%20in%20after%20their%20scheduled%20start%20time.%20I%20also%20want%20a%20remark%20of%20%22undertime%22%20if%20they%20log%20out%20too%20early%20from%20their%20scheduled%20logout.%20I%20want%20to%20have%20this%20data%20in%20a%20pivot%20table%20but%20I've%20been%20having%20a%20hard%20time%20doing%20this.%20If%20anyone%20can%20suggest%20a%20formula%20or%20what's%20the%20best%20thing%20to%20do%20in%20this%20scenario%2C%20I'd%20greatly%20appreciate%20it.%20Kindly%20note%20also%20that%20there%20are%20employees%20who%20are%20scheduled%20at%2012AM%20and%20understandably%20they%20log%20in%20at%20around%2011%20PM%20the%20previous%20day%2C%20how%20am%20I%20going%20to%20include%20them%20in%20the%20attendance%20if%20the%20file%20will%20only%20capture%20their%20login%20for%20today%20(sometimes%20they%20log%20in%20at%2011PM%20the%20previous%20day%2C%20then%20they%20log%20in%20again%20at%201AM%2C%20and%20as%20a%20result%2C%20they%20are%20marked%20as%20tardy).%3C%2FP%3E%3CP%3EI've%20attached%20a%20sample%20file%20for%20everyone%20to%20check.%20In%20the%20file%2C%20I%20only%20include%204%20employees%2C%20but%20in%20reality%2C%20we%20have%20a%20hundred%20employees%20and%20sometimes%20more%20than%201000%20login%2Flogout%20records%2C%20and%20as%20you%20may%20understand%20it's%20difficult%20to%20check%20their%20logins%20manually%20so%20I'd%20really%20appreciate%20it%20if%20anyone%20can%20help%20with%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2052557%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2052872%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2052872%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F901137%22%20target%3D%22_blank%22%3E%40Maryse1227%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20help%20you%20on%20this.%20I%20have%20a%20question%20about%20the%20Employee%20C.%20When%20you%20say%20logout%20at%2012%3A01AM%2C%20it%20should%20be%20the%20next%20day.%20Are%20you%20sure%20about%20the%20example%20given%20%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SantoshKAMALA_0-1610460938695.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F245658i20F6ED57445FBC5B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22SantoshKAMALA_0-1610460938695.png%22%20alt%3D%22SantoshKAMALA_0-1610460938695.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20the%20logout%2011%2F01%2F2021%20or%2012%2F01%2F2021%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2052924%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2052924%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F926212%22%20target%3D%22_blank%22%3E%40SantoshKAMALA%3C%2FA%3E%26nbsp%3Bhello%2C%20thanks%20so%20much%20for%20the%20reply.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20about%20the%20error%2C%20it%20should%20be%20the%20one%20below.%20Basically%2C%20Employee%20C%20has%202%20logins%20captured%2C%20the%20first%20is%20at%2011%3A36%20PM%20of%20January%2010th%20and%20the%20second%20login%20is%20at%201%3A01%20AM%20of%20January%2011th.%20His%20scheduled%20login%20is%20at%2012%3A00%20AM%20of%20January%2011th.%20How%20can%20I%20compute%20his%20login%20and%20make%20sure%20he%20is%20not%20marked%20as%20Tardy%3F%20If%20I%20run%20a%20report%20for%20January%2011th%2C%20it%20will%20only%20capture%20the%201%3A01%20login%20as%20the%20first%20login%20and%20not%20the%20login%20at%2011%3A36%20PM%20the%20previous%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture.JPG%22%20style%3D%22width%3A%20371px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F245661i53A03833EA36B813%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Capture.JPG%22%20alt%3D%22Capture.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2052940%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2052940%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F926212%22%20target%3D%22_blank%22%3E%40SantoshKAMALA%3C%2FA%3E%26nbsp%3B%20And%20yes%2C%20to%20answer%20your%20question%20-%20the%2012%3A01%20AM%20logout%20is%20for%20the%20next%20day.%3C%2FP%3E%3CP%3ESo%2C%20first%20login%20is%20at%2011%3A36%20PM%20January%2010th%20and%20logout%20at%2012%3A01%20AM%20January%2011th.%20They%20then%20log%20back%20in%20again%20at%201%3A01%20AM%20January%2011th%20and%20log%20out%20at%209%3A05%20AM%20January%2011th.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi everyone,

 

I have an attendance file with our employees' schedules and their login and logout record. The problem is, they have multiple logins/logouts. What I want to do is get the first login and the last logout of each employee, and a remark of "tardy" if they log in after their scheduled start time. I also want a remark of "undertime" if they log out too early from their scheduled logout. I want to have this data in a pivot table but I've been having a hard time doing this. If anyone can suggest a formula or what's the best thing to do in this scenario, I'd greatly appreciate it. Kindly note also that there are employees who are scheduled at 12AM and understandably they log in at around 11 PM the previous day, how am I going to include them in the attendance if the file will only capture their login for today (sometimes they log in at 11PM the previous day, then they log in again at 1AM, and as a result, they are marked as tardy).

I've attached a sample file for everyone to check. In the file, I only include 4 employees, but in reality, we have a hundred employees and sometimes more than 1000 login/logout records, and as you may understand it's difficult to check their logins manually so I'd really appreciate it if anyone can help with this.

 

3 Replies

@Maryse1227 

 

Hello,

 

I can help you on this. I have a question about the Employee C. When you say logout at 12:01AM, it should be the next day. Are you sure about the example given ?

SantoshKAMALA_0-1610460938695.png

 

Is the logout 11/01/2021 or 12/01/2021 ?

 

@SantoshKAMALA hello, thanks so much for the reply.

 

Sorry about the error, it should be the one below. Basically, Employee C has 2 logins captured, the first is at 11:36 PM of January 10th and the second login is at 1:01 AM of January 11th. His scheduled login is at 12:00 AM of January 11th. How can I compute his login and make sure he is not marked as Tardy? If I run a report for January 11th, it will only capture the 1:01 login as the first login and not the login at 11:36 PM the previous day.

 

Capture.JPG

 

 

@SantoshKAMALA  And yes, to answer your question - the 12:01 AM logout is for the next day.

So, first login is at 11:36 PM January 10th and logout at 12:01 AM January 11th. They then log back in again at 1:01 AM January 11th and log out at 9:05 AM January 11th.