Formula Based Scoring Time Between Two Dates

[pip74205]

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.

E	F	G	H	I
Date	Day	Start Time	End Time	Hours
3/1/2018	Thursday	6:30 AM	3:00 PM	8:30

<tbody>
</tbody>

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.

 

[Kamolga]

Hi,

I think I got it. The first thing I did was creating a points table on empty sheet that I called Points

[/URL][/IMG]

And then I took your example, added a column points that would sum the 24 calculation columns to the right (that you can hide)

[/URL][/IMG]

The calculation column look at the points for the hour on that day and multiply by 1 if full hour worked, ratio of minutes if the hour was not complete and 0 if it was not worked.

You can download the file here

https://1drv.ms/x/s!AvmGsNl7aaaAgvQyohtg6uHOPNklgw