# Dynamic wage calculation

**URL:** <https://community.glideapps.com/t/dynamic-wage-calculation/71288>\
**Category:** Ask for Help\
**Created:** [February 29, 2024, 1:59pm UTC](https://community.glideapps.com/t/dynamic-wage-calculation/71288 "2024-02-29T13:59:53Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![aferguson024](https://avatars.discourse-cdn.com/v4/letter/a/278dde/32.png) [@aferguson024](https://community.glideapps.com/u/aferguson024)\
**Post date:** [February 29, 2024, 1:59pm UTC](https://community.glideapps.com/t/dynamic-wage-calculation/71288/1 "2024-02-29T13:59:53Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [February 29, 2024, 2:26pm UTC](https://community.glideapps.com/t/dynamic-wage-calculation/71288/2 "2024-02-29T14:26:21Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![aferguson024](https://avatars.discourse-cdn.com/v4/letter/a/278dde/32.png) [@aferguson024](https://community.glideapps.com/u/aferguson024)\
**Post date:** [February 29, 2024, 4:12pm UTC](https://community.glideapps.com/t/dynamic-wage-calculation/71288/4 "2024-02-29T16:12:08Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [February 29, 2024, 4:53pm UTC](https://community.glideapps.com/t/dynamic-wage-calculation/71288/5 "2024-02-29T16:53:03Z")

</div>

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.

```auto
DAY(startDate)

```

```auto
DAY(endDate)

```

```auto
HOUR(startDate)

```

```auto
HOUR(endDate)

```

Next, you need two IF columns structured like this.

```auto
IF Start Day <> Day THEN 'false'
ELSEIF Start Hour > Hour THEN 'false'
ELSE 'true'

```

```auto
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.

```auto
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.

---

<div class="post-metadata">

**Author:** ![aferguson024](https://avatars.discourse-cdn.com/v4/letter/a/278dde/32.png) [@aferguson024](https://community.glideapps.com/u/aferguson024)\
**Post date:** [March 1, 2024, 12:03pm UTC](https://community.glideapps.com/t/dynamic-wage-calculation/71288/6 "2024-03-01T12:03:31Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![mattwensing](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mattwensing/32/72905_2.png) [@mattwensing](https://community.glideapps.com/u/mattwensing)\
**Post date:** [March 31, 2024, 3:48pm UTC](https://community.glideapps.com/t/dynamic-wage-calculation/71288/7 "2024-03-31T15:48:39Z")

</div>

Did this work for you?