This use case example is applicable for a single employee weekly timesheet where there is a fixed number of rows in a table, the user in inputting their hours worked in a week, and at the bottom of the table, the total regular and overtime hours are automatically calculated.
To start, navigate your cursor to the left tab and use a 'Prefilled' table under 'Table'. Since this is a weekly timesheet, we have a fixed number of rows. Set your columns are required. Usually, the table should closely look like this:
Day
| Start Shift (Time field) | End Shift (Time field) | Total Break Hours (Number field) | Regular Hours Worked (Formula field) | Overtime (Formula field) |
Monday | B1 | C1 | D1 | E1 | F1 |
Tuesday | B2 | C2 | D2 | E2 | F2 |
Wednesday | B3 | C3 | D3 | E3 | F3 |
Thursday | B4 | C4 | D4 | E4 | F4 |
Friday | B5 | C5 | D5 | E5 | F5 |
Saturday | B6 | C6 | D6 | E6 | F6 |
Sunday | B7 | C7 | D7 | E7 | F7 |
Total hours worked | (Formula field) - B8 |
| Total | (Formula field) -E8 | (Formula field) - F8 |
To calculate regular hours worked per day (Mon, Tue, Wed, Thu, Fri, Sat, Sun)
=IF(TIMEDIF(B1,C1)-D1>8,8,TIMEDIF(B1,C1)-D1)
This formula is for Monday
To change into the the following days, simply change the number of the cells
To calculate the overtime hours worked per day (Mon, Tue, Wed, Thu, Fri, Sat, Sun)
=IF(TIMEDIF(B1,C1)-D1>8,TIMEDIF(B1,C1)-D1-8,0)
This formula is for Monday
To change into the the following days, simply change the number of the cells
To calculate,
Total hours worked at cell B8:
=E8+F8
This formula is the TOTAL hours worked regardless of regular hours or overtime
Total regular hours worked at cell E8:
=SUM(E1,E2,E3,E4,E5,E6,E7)
Total overtime hours worked at cell F8:
=SUM(F1,F2,F3,F4,F5,F6,F7)