I hope the information provided below is no too much. I just wanted to make sure that I covered all the scenarios so you had all the information. Below the rules are, in the last paragraph is the specific column information.
I need to calculate the hours worked based on the following rules:
If the employee arrives within 7 minutes before or after the scheduled start/end time, then calculate the time worked from the scheduled start and end times in hundredths of a minute (rounding the time to the nearest quarter of an hour). Our system is set up so an employee is unable to clock in any earlier than 7 minutes prior to their start time so this will not be an issue.
Example: Start time is 0800; End Time is 1600; Clock In is 0753; Clock Out is 1607 = total hours to pay should be 8
Actual times to be paid 0800 to 1600
If the employee arrives 8 minutes or more after the scheduled start time but leaves within 7 minutes before or after the scheduled end time, then calculate the time worked using the actual clock in and round the clock out time to their scheduled end time (rounding the time to the nearest quarter of an hour). Total hours worked should be calculated in hundredths of a minute.
Example: Start time is 0800; End Time is 1600; Clock In is 0809; Clock Out is 1607 = total hours to pay should be 7.85
Actual times to be paid 0809 to 1600
If the employee arrives within 7 minutes before or after the scheduled start but clocks out earlier or later than 8 minutes than the scheduled out time, then calculate the time worked by rounding the clock in time their scheduled in time (rounding the time to the nearest quarter of an hour) and using the actual clock out time. Total hours worked should be calculated in hundredths of a minute.
Example 1: Start time is 0800; End Time is 1600; Clock In is 0806; Clock Out is 1610 = total hours to pay should be 8.17
Actual times to be paid 0800 to 1610
Example 2: Start time is 0800; End Time is 1600; Clock In is 0759; Clock Out is 1521 = total hours to pay should be 7.35
Actual times to be paid 0800 to 1521
On my spreadsheet, the times are exported out of our system and appear in this format: ="16:00". I know I can do a =value formula to correct this so the times are read correctly to calculate the formula, but I am not sure if this can somehow be built into the formula itself? If not, I can just do this manually. The number of rows will vary from week to week (should you decide to create a macro to do this). I want my result to appear in column O
Scheduled Start Time is in column F
Scheduled End Time is in column G
Actual Clock In Time is in column I
Actual Clock In Time is in column J
TYIA
I need to calculate the hours worked based on the following rules:
If the employee arrives within 7 minutes before or after the scheduled start/end time, then calculate the time worked from the scheduled start and end times in hundredths of a minute (rounding the time to the nearest quarter of an hour). Our system is set up so an employee is unable to clock in any earlier than 7 minutes prior to their start time so this will not be an issue.
Example: Start time is 0800; End Time is 1600; Clock In is 0753; Clock Out is 1607 = total hours to pay should be 8
Actual times to be paid 0800 to 1600
If the employee arrives 8 minutes or more after the scheduled start time but leaves within 7 minutes before or after the scheduled end time, then calculate the time worked using the actual clock in and round the clock out time to their scheduled end time (rounding the time to the nearest quarter of an hour). Total hours worked should be calculated in hundredths of a minute.
Example: Start time is 0800; End Time is 1600; Clock In is 0809; Clock Out is 1607 = total hours to pay should be 7.85
Actual times to be paid 0809 to 1600
If the employee arrives within 7 minutes before or after the scheduled start but clocks out earlier or later than 8 minutes than the scheduled out time, then calculate the time worked by rounding the clock in time their scheduled in time (rounding the time to the nearest quarter of an hour) and using the actual clock out time. Total hours worked should be calculated in hundredths of a minute.
Example 1: Start time is 0800; End Time is 1600; Clock In is 0806; Clock Out is 1610 = total hours to pay should be 8.17
Actual times to be paid 0800 to 1610
Example 2: Start time is 0800; End Time is 1600; Clock In is 0759; Clock Out is 1521 = total hours to pay should be 7.35
Actual times to be paid 0800 to 1521
On my spreadsheet, the times are exported out of our system and appear in this format: ="16:00". I know I can do a =value formula to correct this so the times are read correctly to calculate the formula, but I am not sure if this can somehow be built into the formula itself? If not, I can just do this manually. The number of rows will vary from week to week (should you decide to create a macro to do this). I want my result to appear in column O
Scheduled Start Time is in column F
Scheduled End Time is in column G
Actual Clock In Time is in column I
Actual Clock In Time is in column J
TYIA
