Timesheet App Overtime Formula

[Sinethemba_Gayi]

Hi,

Can I please be assisted with:

Calculating Regular Hours Worked (should be 8 hours or less) from Total Hours Worked
Calculating Overtime Hours if an employee worked over 8 Hours? (taken from Total Hours Worked
Calculating Overtime Pay based on the above?

I have the Google Sheet formulas but Appsheet doesnt recognise the Regular Hours formular and gives a - sum

This is the speardsheet:

[LeventK]

@Sinethemba_Gayiza
I have already responded to this query in your earlier post. To make the duration calculation correctly, I remember that we have discussed that your [Time In] and [Time Out] columns shall be Datetime type also here:

Timesheet App Questions

Hi, My Regular Hours column or App Formula displays Total Hours with a - if timezones are between AM and Pm or over 8 Hours: Examples belows Total Hours -6:43 -16646224:00 Overtime Hours -16646222:44 How can I rectify this to give me something like Total Hours = 10:00 or 15:00

Assuming that:
[Total Hours], [Regular Hours], [Overtime Hours] being Duration type:

1.) To calculate [Overtime Hours] with AppFormula (remove any ARRAYFORMULA or similar sheet expressions from the gSheet)

[Total Hours] - [Regular Hours]

2.) To calculate [Normal Pay] with AppFormula (remove any ARRAYFORMULA or similar sheet expressions from the gSheet)

(HOUR([Regular Hours]) + MINUTE([Regular Hours]) / 60 + SECOND([Regular Hours]) / 3600) * [Normal Pay]

3.) To calculate [Overtime Pay] with AppFormula (remove any ARRAYFORMULA or similar sheet expressions from the gSheet)

(HOUR([Overtime Hours]) + MINUTE([Overtime Hours]) / 60 + SECOND([Overtime Hours]) / 3600) * [Overtime Pay]

4.) To calculate [Total Wages] with AppFormula (remove any ARRAYFORMULA or similar sheet expressions from the gSheet)

[Normal Pay] + [Overtime Pay]

[Sinethemba_Gayi]

Thanks @LeventK, I did not see the above reply after I responded to your question. Thank you again.

[LeventK]

@Sinethemba_Gayiza
May I ask what kinda sheet formula you have for calculating the [Regular Hours] column values?

[Sinethemba_Gayi]

I do not have a sheet formula, only relied on data captured by Appsheet

[LeventK]

Sinethemba_Gayiza:

I have the Google Sheet formulas but Appsheet doesnt recognise the Regular Hours formular and gives a - sum

I asked for this statement, but now you say that you don’t have a sheet formula??

[Sinethemba_Gayi]

I mistaken your question with Total Hours. Here is the formula

=ARRAYFORMULA(IF(LEN(E2:E), IF(TIMEVALUE(E2:E)>0.3333333333, TEXT(0.3333333333, “hh:mm”),E2:E),))

[LeventK]

@Sinethemba_Gayiza
You don’t need that ARRAYFORMULA. Remove it away from the sheet first. And as I have said earlier, your [Time In] and [Time Out] columns shall be Datetime type and [Total Hours] shall be Duration type. Then you can use a simple AppFormula to calculate the difference:

[Time Out] - [Time In]

And I believe you use this ARRAYFORMULA to assign a [Regular Hour] value, if [Total Hours] value is greater than 8hrs (because 0.3333333333 is the TIMEVALUE for 08:00:00). You can reach the same with setting an AppFormula like this in [Regular Hours] column (remove away the ARRAYFORMULA in the gSheet and the column type shall be Duration as well)

IFS(	
	HOUR([Total Hours]) >= 8,"08:00:00",
	TRUE,[Total Hours]
)

[Sinethemba_Gayi]

Just to be clear or to be on the same understanding, you are recommending that I only use App formula and not use Google Sheet formula?

As you have alluded on your previous replies.

[LeventK]

@Sinethemba_Gayiza
Correct. You don’t need the sheet formulas. AppFormula will be more than enough for a simple sheet and app like this.

[Sinethemba_Gayi]

Hi, I keep getting errors whenever I try to input this command " And I believe you use this ARRAYFORMULA to assign a [Regular Hour] value, if [Total Hours] value is greater than 8hrs ( because 0.3333333333 is the TIMEVALUE for 08:00:00 ). You can reach the same with setting an AppFormula like this in [Regular Hours] column ( remove away the ARRAYFORMULA in the gSheet and the column type shall be Duration as well )

IFS(	
	HOUR([Total Hours]) >= 8,"08:00:00",
	TRUE,[Total Hours]
)

I am attempting to input it on Regular Hours -> Auto Compute -> App Formula and this is the error message I get

“IFS function is used incorrectly:Inputs to IFS() must be condition-value pairs. Input 4 is not a value of a consistent type.”

[Sinethemba_Gayi]

The DateTime function in TimeOut returns a 1899/12/30 date. I was able to fix this is TimeIn by setting NOW() in Initial Value. If I set the same for TimeOut, it captures the same data as in TimeIn.

Also, the Employee rates (Normal Pay Rate and Overtime Rate) don’t appear automatically when the user ClockIn, the App requires the rates to be manually inputted

[LeventK]

@Sinethemba_Gayiza
[Time In] and [TimeOut] columns shall be Datetime type and [Total Hours], [Regular Hours] and [Overtime Hours] columns shall be Duration type. Check them if they are correctly set.

On how to set the price column calculations, please refer to my initial reply above as it explains the expressions in detail.

[Sinethemba_Gayi]

@LeventK They are correctly set as per your instruction

[LeventK]

@Sinethemba_Gayiza
Please check your gSheet as well. Your [Time In] and [Time Out] columns have time values only in the sheet Please change them all to Datetime and apply a DateTime format to those columns in the gSheet as well.

[Sinethemba_Gayi]

Thank you @LeventK, I have diagnosed that the culprit for me getting a -1046100:28:14 Regular Hours is because the DateTime format has an auto computed date of 12/30/1899, so the App calculates starting from 1899.

[LeventK]

@Sinethemba_Gayiza
Datetime type columns - solid with the name - is truly date and time values i.e. 5/3/2019 15:44:00
. However, as I have already explained earlier Time columns are evaluated as Joda Time therefore:
15:44:00 time value is evaluated as 12/31/1899 15:44:00
Your columns are set as DateTime but your sheet values contain TIME VALUE ONLY therefore they are all appended with Joda Date during calculation. Just edit your sheet value in [Time In] & [Time Out] columns to reflect correct DateTime value and appear in MM/DD/YYYY HH:MM format explicitly.

[Sinethemba_Gayi]

I did change TimeIn and TimeOut format to DateTime before I submitted the question.

Let me delete the copy and restart again

[John_Krenkel]

I am wondering if there is a way to do the calculation over the entire pay week. So instead of calculating daily, make the calculation on the entire week.

I have the formula working daily but there is a little glitch. The employee clocks in, clocks out for lunch, clocks in again them out. So if the time for that line isn’t over 8, it doesn’t calculate correctly.

My preference would be to figure out how to make the calculation across the week.

Ideas?

[John_Krenkel]

@Steve

Would you be able to help with this one. Have a timesheet app and want to be able to calculate payroll across a week instead of a line. There are two things happening the way it stands now, written as above.

1. It will calculate overtime if a line is over 8 hours, but the employees are clocking out for lunch then clocking back in upon their return. so they will have 4 hours in the am and 4 hour in the pm but in cases of OT, those hours are not added together.

2. It would be ideal to calulate payroll per week with OT only being over 40 hours.

Thanks

John