Dear all, I am stuck on creating an attendance formula as described below. Our workday runs from 8:00 to 17:00. How can we use the "IF" formula to calculate late arrivals and early departures precisely? Can anybody help me out, please?
Emp Name | Time In | Late Arrival | Time Out | Early Departure | Total Hours
Gavin Mulligan | 7:43:00 | 0:17:00 | 16:45:00 | 0:15:00 | 9:02
Regards
From Kuwait, Kuwait
Acknowledge(1)
Amend(0)
2Emp Name | Time In | Late Arrival | Time Out | Early Departure | Total Hours
Gavin Mulligan | 7:43:00 | 0:17:00 | 16:45:00 | 0:15:00 | 9:02
Regards
From Kuwait, Kuwait
Acknowledge(1)Amend(0)
I have given formula and completed the report with additional fields Total hours and Hours worked. Check this report
From India, Eluru
Acknowledge(1)
Amend(0)
From India, Eluru
Acknowledge(1)Amend(0)
To Sathish I have given the place to specify Start time(D1) and End time(G1). There give the data and get the result. SQA, BOSCO-ITS, Yellagiri Hills
From India, Eluru
Acknowledge(0)Amend(0)
From India, Eluru
Acknowledge(0)Amend(0)
Dear Members, please provide me with the formula for setting the attendance target. For example, if I have to set the attendance percentage at 90% for one month for all employees. This information is required for updating the MIS only.
Regards,
From Singapore, Singapore
Acknowledge(0)Amend(0)
Regards,
From Singapore, Singapore
Acknowledge(0)Amend(0)
You can set the attendance percentage in your policy, and follow-up can be done through the Excel attachment. Sort the report by percentage to view the report in order.
Regards,
SQA, BOSCOITS, Yellagiri Hills, TN, South India
From India, Eluru
Acknowledge(1)Amend(0)
Regards,
SQA, BOSCOITS, Yellagiri Hills, TN, South India
From India, Eluru
Acknowledge(1)Amend(0)
Can you please help me solve my problem? I need to calculate the total number of late arrivals in minutes/hours.
Example Scenario
Required Time = 09:00 (in the morning) - 14:00 (in the afternoon) *in case they come in the afternoon.
Grace Period = 00:15
I have already solved the formula for calculating tardiness:
For instance:
"A" = Required Time (09:00) - AM and (14:00) - PM
"B" = Actual Time (09:16) - AM and (14:15) - PM
Actual Calculation
1) B-A = 00:16
2) B-A = 00:15
My problem is how to combine both formulas in one cell to calculate all lateness exceeding 00:15 and to show in another cell whether it's late or on time.
Thank you very much!
From Philippines, Makati
Acknowledge(1)Amend(0)
Example Scenario
Required Time = 09:00 (in the morning) - 14:00 (in the afternoon) *in case they come in the afternoon.
Grace Period = 00:15
I have already solved the formula for calculating tardiness:
For instance:
"A" = Required Time (09:00) - AM and (14:00) - PM
"B" = Actual Time (09:16) - AM and (14:15) - PM
Actual Calculation
1) B-A = 00:16
2) B-A = 00:15
My problem is how to combine both formulas in one cell to calculate all lateness exceeding 00:15 and to show in another cell whether it's late or on time.
Thank you very much!
From Philippines, Makati
Acknowledge(1)Amend(0)
I have an attendance register, where I mark Weekly offs as W and Leave as L. Using the COUNTIF formula, I calculate the number of leaves taken during a month.
I am facing one problem: if one employee takes leave on Saturday and Monday, my Excel shows it like this - L, W, L. In this case, my leave should be 3, and the weekly off should be 3 (if there are 4 weekly offs in a month).
I need an IF condition formula. Can you help?
From India, Thane
Acknowledge(0)Amend(0)
I am facing one problem: if one employee takes leave on Saturday and Monday, my Excel shows it like this - L, W, L. In this case, my leave should be 3, and the weekly off should be 3 (if there are 4 weekly offs in a month).
I need an IF condition formula. Can you help?
From India, Thane
Acknowledge(0)Amend(0)
Hi all,
Please help me with this. I need a formula for an attendance sheet where I can calculate half-day attendance for today.
For example, if there are 4 working days, 2.5 days present, 0 absent days, and 1 half-day, my Excel sheet currently shows the total days present as 3.0. However, I need it to display the total days present as 2.5.
Thank you.
From India
Acknowledge(0)Amend(0)
Please help me with this. I need a formula for an attendance sheet where I can calculate half-day attendance for today.
For example, if there are 4 working days, 2.5 days present, 0 absent days, and 1 half-day, my Excel sheet currently shows the total days present as 3.0. However, I need it to display the total days present as 2.5.
Thank you.
From India
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 FORMULA.xls