I have a worksheet where scheduled In-Time & Out-Time for employees are alloted. In another sheet there are the actual In-Time of the employees taken from a biometric instrument.
Is there any way to calculate the late entries for the employees.
I have a worksheet where scheduled In-Time & Out-Time for employees are alloted. In another sheet there are the actual In-Time of the employees taken from a biometric instrument.
Is there any way to calculate the late entries for the employees.
What would some expected answers look like?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
It will be the time-difference between scheduled In-Time (given in the master sheet) and actual In-Time (recorded in the In sheet). As there are some employees who has multiple shifts in a month, that makes the situation complicated.
This proposed solution employs three helper columns (D:F) on the 'Late' sheet. The helper columns, which display the starting shift times for each employee, are populated using: =IFERROR(VALUE(LEFT(Settings!D5,5)),"")
The formula that populates the range G5:AH29 on the 'Late' sheet is:Formula:Please Login or Register to view this content.
The formula is set up to find the shift time that is closest to the actual sign in time and calculate the length of tardiness, if any, using that shift's start time. This appears to work, see the first employee 17 for the 1st and 2nd of Feb, however I encourage you to check rigorously.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks