This use case example is applicable for weekly timesheets where there are multiple tables used (one default table for each day) and the employees in certain teams 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 team. 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.
For example:
Given that there are 3 teams working:
Project
Team Alpha
Team Beta
Team Charlie
Use the formulas:
To calculate regular hours worked on all tables in cells F+ (Mon, Tue, Wed, Thu, Fri, Sat, Sun)
=IF(TIMEDIF(C1,D1)-E1>8,8,TIMEDIF(C1,D1)-E1)
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 G+ (Mon, Tue, Wed, Thu, Fri, Sat, Sun)
=IF(TIMEDIF(C1,D1)-E1>8,TIMEDIF(C1,D1)-E1-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 H+ (Mon, Tue, Wed, Thu, Fri, Sat, Sun)
=SUM(F1,G1)
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)
| Team (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 | H1 |
Use the same table for Tuesday, Wednesday, Thursday, Friday, and Saturday. All Default tables.
where:
Table 2 Tuesday (Default table)
Table 3 Wednesday (Default table)
Table 4 Thursday (Default table)
Table 5 Friday (Default table)
Table 6 Saturday (Default table)
Table 7 Sunday (Default table)
This is what the summary tables should look like:
Total regular hours worked (Prefilled table)
Team | Mon | Tue | Wed | Thu | Fri | Sat | Total |
Team Alpha | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Total formula |
Team Beta | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Total formula |
Team Charlie | 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!B:B, A1, Table1!F:F)
Total formula:
=SUM(B1,C1,D1,E1,F1,G1)
Total overtime hours worked (Prefilled table)
Team | Mon | Tue | Wed | Thu | Fri | Sat | Total |
Team Alpha | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Total formula |
Team Beta | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Total formula |
Team Charlie | 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!B:B, A1, Table1!G:G)
Total formula:
=SUM(B1,C1,D1,E1,F1,G1)
Total hours worked (Prefilled table)
Team | Mon | Tue | Wed | Thu | Fri | Sat | Total |
Team Alpha | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Total formula |
Team Beta | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Total formula |
Team Charlie | 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!B:B, A1, Table1!H:H)
Total formula:
=SUM(B1,C1,D1,E1,F1,G1)