Count Payroll Overtime Hours

[MteaM_Eilat]

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 ?

[LeventK]

@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
)

[MteaM_Eilat]

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

[LeventK]

@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?

[MteaM_Eilat]

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

[LeventK]

@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.

[MteaM_Eilat]

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

[MteaM_Eilat]

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

[fon1]

How do you calculate working days?

Does your payroll require a text file?

[MteaM_Eilat]

@Steve

Thanks for reTagging

[LeventK]

@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))

[MteaM_Eilat]

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 ?

[LeventK]

@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)))

[MteaM_Eilat]

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.

[MteaM_Eilat]

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

[LeventK]

@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)))

[MteaM_Eilat]

still shows 0 .
sorry