Login and Logout Report

[ely.rodriguez]

Good day,

I need assistance in login logout report. I have attached the file "login_logout".

As you can see on the file, there are two (2) sheets "raw data" and "schedule". On sheet "raw data", it contains the raw data of login and logout of our agents that I have extracted from CMS, which is from May 20 to May 28. On sheet "schedule", obviously it contains the schedule of our agents.

If you check the data on sheet "raw data", you will notice that there are multiple login and logout entries per agents. In addition, there are agents who started their shifts at night, then crossing over to morning the next day, which is their end time.

What I want to do is to capture their actual login and logout time (first login and last logout) basing on the raw data on sheet "raw data", and the result should be shown on the sheet "schedule".

I hope that I explained it well. Thank you very much in advance.

[jeffreybrown]

It would help tremendously if you provided what you expect for results with at least the first few rows. I2, I3, I4 and J2, J3, J4, and K2, K3, K4.

[popipipo]

I think this is what you want.

[FlameRetired]

How about in I2 filled down and across column J formatted for date and time.

Please Login or Register  to view this content.

It makes the math accounting for work past midnight simpler in column K.

[ely.rodriguez]

Thanks for the response popipipo, but that's not what I am looking for.

Hi jeffreybrown, I have attached the file again which includes the result that I want...

[ely.rodriguez]

Sorry, please use this file attached instead...

[jeffreybrown]

How does this look...

[ely.rodriguez]

HI jeffreybrown,

Thanks for the response. This works almost perfect. However, I found some problem. There are agents that their login and logout were not captured correctly. I have attached your file and filtered it to those agents that are having issues.

Please advise. Thank you very much.

[jeffreybrown]

What would you expect us to do with information that was loaded incorrectly? The formula is reporting what's captured from the "raw data" tab.

Do you want to highlight those three records in some way?

K2 copied down >> =IF(N(J2),IF((J2+H2)-((G2+I2))>=1,"24 Hours",((J2+H2)-((G2+I2)))),"")

You could also apply conditional formatting to highlight the erroneous row of data.

Conditional Formatting

• Highlight applicable range >> $A$2:$K$267
• Home Tab >> Styles >> Conditional Formatting
• New Rule >> Use a formula to determine which cells to format
• Edit the Rule Description: Format values where this formula is true: =$K2="24 Hours"
• Format… [Number, Font, Border, Fill]
• OK >> OK

Will this work?

[ely.rodriguez]

Hi jeffreybrown,

I think this is fine for now. Thank you very much for the help. Have a wonderful day ahead!...