This use case example is applicable for weekly timesheets where there are multiple tables used (one default table for each day) and the employees who worked on that day are logged.
In a separate table, a summary of the total hours, total regular hours, and overtime hours and displayed per person. In this case, we'll make use of the SUMIF() formula.
To start, navigate your cursor to the left tab and use a 'Default' table under 'Table'. Set your columns are required.
Given that you have a dropdown list of employees:
Juan
Emilio
Mercado
To calculate regular hours worked on all tables in cells E+ (Mon, Tue, Wed, Thu, Fri, Sat, Sun)
=IF(TIMEDIF(B1,C1)-D1>8,8,TIMEDIF(B1,C1)-D1)
Use this formula for all E1 cells in each table. When you add more logs when you fill out the form, the formula will automatically populate.
To calculate the overtime hours worked per day on all tables in cells F+ (Mon, Tue, Wed, Thu, Fri, Sat, Sun)
=IF(TIMEDIF(B1,C1)-D1>8,TIMEDIF(B1,C1)-D1-8,0)
Use this formula for all F1 cells in each table. When you add more logs when you fill out the form, the formula will automatically populate.
To calculate the total hours worked per day on all tables in cells G+ (Mon, Tue, Wed, Thu, Fri, Sat, Sun)
=SUM(E1,F1)
Use this formula for all G1 cells in each table. When you add more logs when you fill out the form, the formula will automatically populate.
Usually, the tables should closely look like these:
Table 1 Monday (Default table)
Employee Name (Dropdown list)
| Start Shift (Time field) | End Shift (Time field) | Total Break Hours (Number field) | Regular Hours Worked (Formula field) | Overtime (Formula field) | Total Hours |
A1 | B1 | C1 | D1 | E1 | F1 | G1 |
Table 2 Tuesday (Default table)
Employee Name (Dropdown list)
| Start Shift (Time field) | End Shift (Time field) | Total Break Hours (Number field) | Regular Hours Worked (Formula field) | Overtime (Formula field) | Total Hours |
A1 | B1 | C1 | D1 | E1 | F1 | G1 |
Table 3 Wednesday (Default table)
Employee Name (Dropdown list)
| Start Shift (Time field) | End Shift (Time field) | Total Break Hours (Number field) | Regular Hours Worked (Formula field) | Overtime (Formula field) | Total Hours |
A1 | B1 | C1 | D1 | E1 | F1 | G1 |
Table 4 Thursday (Default table)
Employee Name (Dropdown list)
| Start Shift (Time field) | End Shift (Time field) | Total Break Hours (Number field) | Regular Hours Worked (Formula field) | Overtime (Formula field) | Total Hours |
A1 | B1 | C1 | D1 | E1 | F1 | G1 |
Table 5 Friday (Default table)
Employee Name (Dropdown list)
| Start Shift (Time field) | End Shift (Time field) | Total Break Hours (Number field) | Regular Hours Worked (Formula field) | Overtime (Formula field) | Total Hours |
A1 | B1 | C1 | D1 | E1 | F1 | G1 |
Table 6 Saturday (Default table)
Employee Name (Dropdown list)
| Start Shift (Time field) | End Shift (Time field) | Total Break Hours (Number field) | Regular Hours Worked (Formula field) | Overtime (Formula field) | Total Hours |
A1 | B1 | C1 | D1 | E1 | F1 | G1 |
Table 7 Sunday (Default table)
Employee Name (Dropdown list)
| Start Shift (Time field) | End Shift (Time field) | Total Break Hours (Number field) | Regular Hours Worked (Formula field) | Overtime (Formula field) | Total Hours |
A1 | B1 | C1 | D1 | E1 | F1 | G1 |
This is what the summary tables should look like:
Total regular hours worked
Employee | Mon | Tue | Wed | Thu | Fri | Sat | Total |
(Dropdown) | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Total formula |
Mon-Sat formula: (Note: Change the Table_n accordingly)
=SUMIF(Table1!A:A, A1, Table1!E:E)
Total formula:
=SUM(B1,C1,D1,E1,F1,G1)
Total overtime hours worked
Employee | Mon | Tue | Wed | Thu | Fri | Sat | Total |
(Dropdown) | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Total formula |
Mon-Sat formula: (Note: Change the Table_n accordingly)
=SUMIF(Table1!A:A, A1, Table1!F:F)
Total formula:
=SUM(B1,C1,D1,E1,F1,G1)
Total hours worked
Employee | Mon | Tue | Wed | Thu | Fri | Sat | Total |
(Dropdown) | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Total formula |
Mon-Sat formula: (Note: Change the Table_n accordingly)
=SUMIF(Table1!A:A, A1, Table1!G:G)
Total formula:
=SUM(B1,C1,D1,E1,F1,G1)