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
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:
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)
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 )
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.
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.
@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.
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.
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.
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.
It would be ideal to calulate payroll per week with OT only being over 40 hours.