Hello all. My work is instituting an incentive plan to encourage overtime when needed. They decided on a point system where you earn points for each hour you work of overtime. Each hour of each day may be worth a different point. As an example: Working 6:00 PM - 7:00 PM on Tuesday would be 1 point for the hour, while working 6:00 PM - 7:00 PM on Saturday would be worth 5 points for that hour, and 8:00 AM - 9:00 AM on Saturday would be 3 points for that hour. Now I need to come up with the way to track it. And I have come to you wonderful people hoping to help me figure out an easy way to do this. Thank you so much for any help you can provide.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"] I[/TD]
[/TR]
[TR]
[TD="align: center"]Date[/TD]
[TD="align: center"]Day[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]End Time[/TD]
[TD="align: center"]Hours[/TD]
[/TR]
[TR]
[TD="align: center"]3/1/2018[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]6:30 AM[/TD]
[TD="align: center"]3:00 PM[/TD]
[TD="align: center"]8:30[/TD]
[/TR]
</tbody>[/TABLE]
The above table is the key cells on the current overtime sheet we use. The first row is the Column letter. Hopefully, the formula to calculate the points for this can fit in one column and can be utilized for hundreds of rows without slowing the computer. I want to have a separate tab in the sheet listing the point values for each hour, so if there is a change, it can be easily updated. How that table looks is whatever works easiest to get the correct points.
Again, thank you for any help you can provide. This has me stuck.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"] I[/TD]
[/TR]
[TR]
[TD="align: center"]Date[/TD]
[TD="align: center"]Day[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]End Time[/TD]
[TD="align: center"]Hours[/TD]
[/TR]
[TR]
[TD="align: center"]3/1/2018[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]6:30 AM[/TD]
[TD="align: center"]3:00 PM[/TD]
[TD="align: center"]8:30[/TD]
[/TR]
</tbody>[/TABLE]
The above table is the key cells on the current overtime sheet we use. The first row is the Column letter. Hopefully, the formula to calculate the points for this can fit in one column and can be utilized for hundreds of rows without slowing the computer. I want to have a separate tab in the sheet listing the point values for each hour, so if there is a change, it can be easily updated. How that table looks is whatever works easiest to get the correct points.
Again, thank you for any help you can provide. This has me stuck.