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 and I just opened citehr.com and saw your question. Even I too don't know how to calculate timings in Excel, but searched some formulas on the internet and tried myself, and I was successful.
To calculate who leaves early and late login, first, you find the difference between login (A column) and logout (B column). Just subtract Logout (B column) time from login (A column) time. You will get the difference there and easily recognize who is not staying around 9 hours per day.
But there is a small problem there. If logout (B column) time is less than (<) login (A column) time, then it shows 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 and format cells, 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)
As I don't have any work in my office today and I just opened citehr.com and saw your question. Even I too don't know how to calculate timings in Excel, but searched some formulas on the internet and tried myself, and I was successful.
To calculate who leaves early and late login, first, you find the difference between login (A column) and logout (B column). Just subtract Logout (B column) time from login (A column) time. You will get the difference there and easily recognize who is not staying around 9 hours per day.
But there is a small problem there. If logout (B column) time is less than (<) login (A column) time, then it shows 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 and format cells, 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)
Hi, Santhosh,
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.
The actual office working hours are from 09:30 am to 06:00 pm.
Employee:
- 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)
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.
The actual office working hours are from 09:30 am to 06:00 pm.
Employee:
- 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)
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)
Hi Shariff,
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 log out 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)
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 log out 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)
Hi Santhosh,
Firstly, thank you. What I want is I am not getting it, so please see the attachment and get it. The attachment is very good; it is so simple anyone can understand it. The formatting of cells in red and green is an excellent idea. But Santhosh Bhai, go through the attachment and help me, please. Pleaseeee.
Thanks & Regards,
Shariff
From India, Bangalore
Acknowledge(0)Amend(0)
Firstly, thank you. What I want is I am not getting it, so please see the attachment and get it. The attachment is very good; it is so simple anyone can understand it. The formatting of cells in red and green is an excellent idea. But Santhosh Bhai, go through the attachment and help me, please. Pleaseeee.
Thanks & Regards,
Shariff
From India, Bangalore
Acknowledge(0)Amend(0)
Open the Excel sheet, click on the Windows button (at the top right of the Excel), click on that, and click on "Save As." Select "Excel 97-2003" workbook and save. Try to upload that file. You will surely be able to do it.
Regards,
Santosh
From India, Hyderabad
Acknowledge(0)Amend(0)
Regards,
Santosh
From India, Hyderabad
Acknowledge(0)Amend(0)
atlast i have made it,, to upload the file ,,, Thanks a Lot
From India, Bangalore
Acknowledge(0)Amend(0)
From India, Bangalore
Acknowledge(0)Amend(0)Looking for something specific? - Join & Be Part Of Our Community and get connected with the right people who can help. Our AI-powered platform provides real-time fact-checking, peer-reviewed insights, and a vast historical knowledge base to support your search.
Excel Formulas.xls
Excel Formulas.ppt