Excel Help Needed: How Do I Calculate Latecomers and Early Leavers in One Cell?

Hi, can anyone tell me the Excel formula to calculate latecomers and also those who leave early in one cell? Please help me.

Regards,
Shariff

Hi, can anyone tell me the Excel formula to calculate latecomers and also those who leave early in one cell? Please email me at [Email Removed For Privacy Reasons].

Regards,
Shariff

Hi Shariff, as I don't have any work in my office today, I just opened citehr.com and saw your question. I also didn't know how to calculate timings in Excel, but I searched for some formulas on the internet, tried them myself, and was successful.

Calculating Early Leaves and Late Logins

To calculate who leaves early and logs in late, first find the difference between the login (A column) and logout (B column). Just subtract the logout (B column) time from the login (A column) time. You will get the difference there and can easily recognize who is not staying around 9 hours per day.

But there is a small problem. If the logout (B column) time is less than (<) the login (A column) time, it will show all #####. In that case, use this formula in Excel: `if(B<A,B+1,B)-A`. You will get the difference.

If you don't want to see AM/PM in those columns, just right-click, format cells, and select h:mm:ss. If you have any doubts, feel free to reach out to me again.

Regards,
Santosh

Thank you very much for your reply and concern. Hmm, I tried the formula, but again, I am getting an error. Okay, let me tell you exactly what I want.

Office Working Hours

The actual office working hours are from 09:30 am to 06:00 pm.

Employee Log Details

- 09:40 log in
- 05:45 log out

In the log-in column, with the help of a general formula, I can identify latecomers. However, when the employee logs in before 09:20, it shows an error. The same issue occurs in the log-out column. I can identify early leavers, but if the time is entered after 06:00, an error occurs. Please help me; for the past 3 days, I have been trying to solve this but cannot figure it out. Your assistance is greatly appreciated.

Regards,
Shariff

Hi Santhosh, I need two formulas for two cells, i.e., one for the log-in column and the other for the log-out column. I want to identify late log-ins, early log-ins, early log-outs, and late log-outs in separate columns. Please assist me with this.

Thank you.

I have calculated some formulas as per your questions in the attached Excel sheet. Let me know if you have any further doubts or questions regarding the formulas in the attached Excel file.

If you have encountered any errors in maintaining or calculating the timings, please provide the login time and logout time so that I can assist you further in detail.

Also, let me know your feedback on the attachment as I have prepared that document specifically for you.

Regards,
Santosh

Please find attached the Excel sheet. I have provided two solutions to calculate the early login timings and early logout timings. Additionally, I have asked a question, so I am waiting for the answer to that question.

Regards,
Santosh

Looking at the attachment that you uploaded, it appears you are using a complicated format. Sticking to your basic question, i.e., calculating Early Login and Late Log Out, please find the attachment that I prepared, which might help you to know, from each employee's perspective, which day he was early/late. Also, as indicated by Santosh, it depends on the time entered in the log-out cell to calculate the difference in time. To ease this out, I inserted a formula (Column F) where if the log-out time is less than the login time, it gives an error.

Please let me know if you need more details.

Regards,
Chakra

Hi, thank you very much for your concern, Boss. What you have sent, the Excel sheet, that's the one I was looking for. Thank you very much.

Regards,
Shariff