Formula to Calculate Time with Multiple Round Rules

spyldbrat

Board Regular
Joined
May 5, 2002
Messages
211
Office Version
  1. 365
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 :)
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Prepare your data in below format and share

Book1
ABCDE
1Start TimeEnd TimeGridPay
27:538:000800 to 16008
38:078:000809 to 16007.85
416:0716:000800 to 16108.17
50800 to 15217.35
Sheet1
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top