Formula Based Scoring Time Between Two Dates

pip74205

New Member
Joined
Feb 7, 2017
Messages
16
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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi,

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

Capture.jpg
[/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)

Capture2.jpg
[/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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,217
Members
452,619
Latest member
Shiv1198

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