hiii,,, can any one tell me the excel formula to calculate late comers and also who leaves early in one cell,, please help me ,,, Regrads Shariff
From India, Bangalore
Acknowledge(0)
Amend(0)
1From India, Bangalore
Acknowledge(0)Amend(0)
hiiii,, can any one tell me the excel formula to calculate late comers and also who leaves early in one cell please mail me Rgds Shariff
From India, Bangalore
Acknowledge(0)Amend(0)
From India, Bangalore
Acknowledge(0)Amend(0)
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
From India, Hyderabad
Acknowledge(0)Amend(0)
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
From India, Hyderabad
Acknowledge(0)Amend(0)
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
From India, Bangalore
Acknowledge(0)Amend(0)
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
From India, Bangalore
Acknowledge(0)Amend(0)
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.
From India, Bangalore
Acknowledge(0)Amend(0)
Thank you.
From India, Bangalore
Acknowledge(0)Amend(0)
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
From India, Hyderabad
Acknowledge(0)Amend(0)
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
From India, Hyderabad
Acknowledge(0)Amend(0)
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
From India, Hyderabad
Acknowledge(0)Amend(0)
Regards,
Santosh
From India, Hyderabad
Acknowledge(0)Amend(0)
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
From India, Hyderabad
Acknowledge(0)Amend(0)
Please let me know if you need more details.
Regards,
Chakra
From India, Hyderabad
Acknowledge(0)Amend(0)CiteHR is an AI-augmented HR knowledge and collaboration platform, enabling HR professionals to solve real-world challenges, validate decisions, and stay ahead through collective intelligence and machine-enhanced guidance. Join Our Platform.
Excel Formulas.xls
Excel Formulas.ppt