Count Payroll Overtime Hours

Hi everybody!

Im building a Payroll Form view which is calculating Employee’s HOURS by DATE. (pulled from ORDERS table. )
And it has different Payment RATES depending on the amount of Hours worked during the day…
They get payed 100% first 8 hours.
They get payed 125% the next two hours (8-10 h)
The get payed 150% the NEXT two hours (10-12 h)
The get payed 150% IF it is saturday.

I’ve managed to pull the Total Hours in between two dates with a virtual columns and the Saturday hours…

And now im trying to pull out the hours devided by the Payment Rates during the day.

Any tips on how can i acheive my goal ?

@MteaM_Eilat
You can construct an expression like this (change the table and column names as fit)

IFS(
	[TOTAL HOURS] <= 8,[RATE],
	AND([TOTAL HOURS]>8,[TOTAL HOURS]<=10),[RATE] * 1.25,
	OR(
		WEEKDAY([DATE])=7,
		AND(
			[TOTAL HOURS]>10,
			[TOTAL HOURS]<=12
		)
	),[RATE] * 1.5
)
1 Like

thanks for replying !

Where should this expression go ?
should i add a virtual column ?

And another questuin please,
I dont see how this expression is splitting in to dates… i mean how does the app recognize each DAY seperatly ?

I add an image of what i’ve achieved.
and what i want to achieve maybe it will help

@MteaM_Eilat
As you have said that you have VC that you have calculated the total hours and saturday hours, I have given you an example of calculating the payroll. But I do now understand that you want to split the worked hours into categories/groups. Am I correct?

yes! correct.
I want to split the hours so i will be able to monitor each Pay Rate

@MteaM_Eilat
Can you a bit tell me about the structure of the table that you sum/lookup those working hours and the relationship between the base table of the form in your images and this working hours table? Thanks.

The Summing table is a FORM.
its related to the Orders table via the FULL NAME key column which includes the Dates and Hours…

this is how it looks now

This is how the Working Hours table structured:

UniqueID Operation Date Status Invoice Name Location Job Address Fixed Price Price\Hour Notes Vat 17% Withholding % Payment Method Manager Employee Charge Hours Hours Employee Invoice ID Phone Contact Name Phone 2 Contact Name 2 Driver Time Start Time End
11082 Mon 01 Apr 2019 Completed וועד בית החשמונאים 9, אילת Avivit , Ivet (HaHashmona’im St 9 ) Building HaHashmona’im St 9 Eilat ₪75.00 0% %0 Monthly Maya Paley Andrey Andreev Charge 3.00 3.00 052 290 1852 Avivit 052 606 2707 Ivet Artur Shteinberg 12:00 15:00

@Steve

Thanks for reTagging :ok_hand:

1 Like

@MteaM_Eilat
Below expression will give you the %125

SELECT(Orders[Hours Employee],AND([Employee]=[Full Name],[Operation Date]>=[Date Start],[Operation Date]<=[Date End],[Hours Employee]>8,[Hours Employee]<=10))

And this will give you the %150

SELECT(Orders[Hours Employee],AND([Employee]=[Full Name],[Operation Date]>=[Date Start],[Operation Date]<=[Date End],[Hours Employee]>10,[Hours Employee]<=12))

Thanks for your effort!

But the expressions seems to be not working… although no ERROR messages appear …
I’ve added Four VC - two for 125% and two for 150%:

one with the expression (list type)
another VC that SUMS the list’s values.

but it shows 0.

any ideas why ?

@MteaM_Eilat
Enclose my expressions with SUM(...)
Below expression will give you the %125

SUM(SELECT(Orders[Hours Employee],AND([Employee]=[Full Name],[Operation Date]>=[Date Start],[Operation Date]<=[Date End],[Hours Employee]>8,[Hours Employee]<=10)))

And this will give you the %150

SUM(SELECT(Orders[Hours Employee],AND([Employee]=[Full Name],[Operation Date]>=[Date Start],[Operation Date]<=[Date End],[Hours Employee]>10,[Hours Employee]<=12)))

Added new VC (decimal type) with your expression.
but still shows me 0 (the employee in the example has 125% & 150% rates for sure) .
attaching an image.

im not sure, but yet i cannot figure out with your expression how the app recognizes each day seperatly to calculate the daily hours?

@MteaM_Eilat
Can you try below expressions?
For %125

SUM(SELECT(Orders[Hours Employee],AND([Employee]=[_THISROW].[Full Name],[Operation Date]>=[_THISROW].[Date Start],[Operation Date]<=[_THISROW].[Date End],[Hours Employee]>8,[Hours Employee]<=10)))

For %150

SUM(SELECT(Orders[Hours Employee],AND([Employee]=[_THISROW].[Full Name],[Operation Date]>=[_THISROW].[Date Start],[Operation Date]<=[_THISROW].[Date End],[Hours Employee]>10,[Hours Employee]<=12)))

still shows 0 .
sorry :roll_eyes: