Hello,
Looking for ideas, all input very gratefully received.
We have a use case where we record timesheets for employees. We need to calculate their wages.
Wages are dynamic based on the job they are working on, the client they are working with and the day/time worked.
For example, let’s say Client A has Job B. For Job B, they pay £10 / hour on weekdays 8am to 8pm, £20 / hour on weeknights 8pm to 8am, and £30 / hour on weekends (say 8pm Friday to 8am Monday).
I can envisage how to build the rate table, e.g. you would have a row in a rate table with say an array for days of the week that rate applies to, and a start time and end time the rate applies for.
What I cannot conceptualise is how to automatically identify rates that match a given shift worked. I emphasise multiple rates because of course a shift could stretch across rates. We need some logic that can break down a shift based on some form of lookup, based on the timings/weekdays found within the shift.
For example, in the below if an employee worked 5pm Thursday to 10am Saturday (I feel sorry for them!) then they would work the following hours:
5pm to 8pm Thursday - 3 hours @ day rate @ £10 = £30
8pm to 8am Thursday to Friday - 12 hours @ night rate @ £20 = £240
8am to 8pm Friday - 12 hours @ day rate @ £10 = £120
8pm to 10am Friday to Saturday - 14 hours @ weekend rate @ £30 = £420
Total Pay: £810
I imagine we’d do this via some form of lookup, but I’m struggling to imagine it.
Any ideas?
1 Like
Do you have any other special rates, such as holiday rates, or are the rates always consistent week to week, day to day, hour to hour?
I realize your example is extreme, but I assume that a shift could cross from one day to the next, but never more than a handful of hours, correct?
Hi there - there are variances to these rates, I’d refer to them as “enhancements” - but for the purposes of this logic I think we’d set these aside for now.
A shift could cross from one day to another correct, but only over a handful of hours as you say. The average rates a shift might cross over would be 1 or 2, but there are extreme cases of 5 or 6 rates in one shift.
So, since we are I ignoring any “enhancements”, this is my thought. Create a table with 168 rows to represent each hour of each day of a week. So you would end up with something like this.
The day number represents the day (1=Sunday, 7=Saturday) and the hour is the hour of the day in 24 hour format
| Day |
Hour |
Rate |
| 1 |
0 |
$10 |
| 1 |
1 |
$10 |
| 1 |
2 |
$10 |
| … |
… |
… |
| 1 |
23 |
$10 |
| 2 |
0 |
$5 |
| 2 |
1 |
$5 |
| … |
… |
… |
| 7 |
23 |
$10 |
So, imagine a user enters the start and end datetimes for their shift.
What you would do is bring those start and end times into this new table with Single Value columns. Then you would create 4 math columns that retrieve the Day number and Hour number from each date.
DAY(startDate)
DAY(endDate)
HOUR(startDate)
HOUR(endDate)
Next, you need two IF columns structured like this.
IF Start Day <> Day THEN 'false'
ELSEIF Start Hour > Hour THEN 'false'
ELSE 'true'
IF End Day <> Day THEN 'false'
ELSEIF End Hour < Hour THEN 'false'
ELSE 'true'
Next, create another IF column that checks if both of the previous IF columns are checked (true) and return the rate. Like this.
IF StartIF is not checked THEN 0
ELSEIF EndIF is not checked THEN 0
ELSE Rate Value
What this IF column should do is return the Rate only if the shift fits within that particular Day/Hour.
Finally you can create a Rollup column that sums the numbers in that final IF column and the result should be the total amount for the entire shift.
3 Likes
Thanks for this Jeff, really appreciated. I’m going to have a play with this logic over the weekend and see what we can produce.
1 Like