This use case example is applicable for a single employee weekly timesheet with no fixed number of rows in a table, the user inputting their hours worked in a week, and at the the summary table, the total regular and overtime hours are automatically calculated. They can have multiple entries per day but the summaries are based from projects.
Lets say on Monday, Employee 1 worked on both Project A and Project B at different hours. On Tuesday, Employee 1 worked on Project C and D. The rest of the week varying as well. Our summary table consists of their total hours in a project.
To start, navigate your cursor to the left tab and use a 'Default' table under 'Table'. Set your columns are required. Usually, the tables should closely look like the ones shown below.
For example:
Given that there are 4 projects the employee is working on within a week.
Project
Project A
Project B
Project C
Project D
Use the formulas:
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.
Table 1 Monday (Default table)
Project(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 |
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)
Use Prefilled tables for your summary tables since the projects are already provided.
This is what the summary tables should look like:
Total regular hours worked (Prefilled table)
Project | Mon | Tue | Wed | Thu | Fri | Sat | Total |
Project A | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Total formula |
Project B | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Total formula |
Project C | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Total formula |
Project D | 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 and A_n accordingly)
=SUMIF(Table1!A:A, A1, Table1!E:E)
Total formula:
=SUM(B1,C1,D1,E1,F1,G1)
Total overtime hours worked (Prefilled table)
Project | Mon | Tue | Wed | Thu | Fri | Sat | Total |
Project A | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Total formula |
Project B | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Total formula |
Project C | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Total formula |
Project D | 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 and A_n accordingly)
=SUMIF(Table1!A:A, A1, Table1!F:F)
Total formula:
=SUM(B1,C1,D1,E1,F1,G1)
Total hours worked
Project | Mon | Tue | Wed | Thu | Fri | Sat | Total |
Project A | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Total formula |
Project B | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Total formula |
Project C | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Mon-Sat formula | Total formula |
Project D | 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 and A_n accordingly)
=SUMIF(Table1!A:A, A1, Table1!G:G)
Total formula:
=SUM(B1,C1,D1,E1,F1,G1)