Learn how to use a formula to automatically calculate Time & Half hours in a single entry Labour timesheet in Dashpivot Web. This formula applies to two scenarios: weekday overtime between hours 8 and 10, and Saturday day shifts up to 2 hours.
Prerequisites
Before you begin, make sure you have the following:
Plan: All Plan Types
Permission: Org Controller (Home Folder) or Project Controller (Team Folder)
Skills Required: Dashpivot, basic formula knowledge
Device Type: Computer
Subscription: Dashpivot Web
Column Setup
Before adding the formula, make sure your Labour table has the following columns in order:
Column | Label | Field Type |
B | Day | Date |
C | Start time | Time |
D | Finish time | Time |
E | Break time | Number |
G | Time & Half | Formula |
Break time must be entered as a decimal number of hours — for example, 0.5 for 30 minutes and 1 for 1 hour.
Step-by-Step Instructions
Open your template in the Template Editor and navigate to your Labour table.
Add a new column and set the field type to Formula.
Label the column Time & Half.
Enter the following formula in the Formula field:
=IF(TIMEDIF(C1,D1)-E1>8, TIMEDIF(C1,D1)-8-E1, 0) * IF(TIMEDIF(C1,D1)-E1<10, 1, 0) * IF(WEEKDAY(B1,2)<=5, 1, 0) * IF((C1>=Timevalue("06:30AM"))*(C1<Timevalue("06:30PM")), 1, 0)+IF(TIMEDIF(C1,D1)-E1>=10, 2, 0) * IF(WEEKDAY(B1,2)<=5, 1, 0) * IF((C1>=Timevalue("06:30AM"))*(C1<Timevalue("06:30PM")), 1, 0)+IF(WEEKDAY(B1,2)=6, 1, 0) * IF(TIMEDIF(C1,D1)<2, TIMEDIF(C1,D1), 0)+IF(WEEKDAY(B1,2)=6, 1, 0) * IF(TIMEDIF(C1,D1)>=2, 2, 0) * IF((C1>=Timevalue("06:30AM"))*(C1<Timevalue("06:30PM")), 1, 0)Save the template.
Understanding the Formula
The formula covers four scenarios or parts across two situations: weekday overtime and Saturday day shifts. Only the applicable part will produce a result for any given row.
Think of each part as a different rule — the formula checks each one and only the rule that matches the situation will produce a result. The others automatically return 0 and stay out of the way.
Key Concepts
TIMEDIF(C1, D1) returns the difference between start and finish time as a decimal number of hours. Net hours worked = TIMEDIF(C1, D1) − E1 (total hours minus break).
Timevalue("06:30AM") / Timevalue("06:30PM") are the shift boundary constants used to determine whether a shift is a day shift or night shift.
WEEKDAY(B1, 2) returns the day of the week as a number, where Monday = 1 and Sunday = 7. Values 1–5 are weekdays; 6 is Saturday; 7 is Sunday.
IF as a gate — many parts of the formula use IF to return 1 (pass) or 0 (block). These are multiplied together so that all conditions must be true for hours to be counted. If any gate returns 0, the entire part equals 0.
Part 1 — Weekday overtime: 8 to 10 hours
IF(TIMEDIF(C1,D1)-E1>8, TIMEDIF(C1,D1)-8-E1, 0)
* IF(TIMEDIF(C1,D1)-E1<10, 1, 0)
* IF(WEEKDAY(B1,2)<=5, 1, 0)
* IF((C1>=Timevalue("06:30AM"))*(C1<Timevalue("06:30PM")), 1, 0)Component | What it checks | Effect if false |
| Net hours exceed 8 | Returns 0 — no T&H |
| Hours beyond 8 (the T&H value) | — |
| Net hours are under 10 | Multiplies by 0 — switches to Part 2 |
| Weekday (Mon–Fri) | Multiplies by 0 |
Day shift condition | Start time between 06:30–18:30 | Multiplies by 0 |
Result when all conditions pass: T&H = net hours − 8. Example: 9.5 net hours → T&H = 1.5 hours.
Part 2 — Weekday overtime: 10 hours or more
IF(TIMEDIF(C1,D1)-E1>=10, 2, 0)
* IF(WEEKDAY(B1,2)<=5, 1, 0)
* IF((C1>=Timevalue("06:30AM"))*(C1<Timevalue("06:30PM")), 1, 0)Component | What it checks | Effect if false |
| Net hours are 10 or more | Returns 0 |
| Hard-coded cap — T&H is always max 2 hrs on a weekday | — |
| Weekday | Multiplies by 0 |
Day shift condition | Day shift | Multiplies by 0 |
Result when all conditions pass: T&H = 2 hours (fixed). Hours beyond 10 go to Double Time.
Part 3 — Saturday day shift, less than 2 hours
IF(WEEKDAY(B1,2)=6, 1, 0) * IF(TIMEDIF(C1,D1)<2, TIMEDIF(C1,D1), 0)
Component | What it checks | Effect if false |
| It is Saturday | Multiplies by 0 |
| Total gross hours are under 2 | Returns 0 — switches to Part 4 |
| Total gross hours (the T&H value) | — |
Note: This part uses gross hours (TIMEDIF(C1,D1)) with no break deducted. This is the only part across all three formulas that doesn't use net hours, and it only applies to very short Saturday shifts under 2 hours total.
Result when all conditions pass: T&H = total gross hours worked.
Part 4 — Saturday day shift, 2 hours or more
IF(WEEKDAY(B1,2)=6, 1, 0)
* IF(TIMEDIF(C1,D1)>=2, 2, 0)
* IF((C1>=Timevalue("06:30AM"))*(C1<Timevalue("06:30PM")), 1, 0)
Component | What it checks | Effect if false |
| Saturday | Multiplies by 0 |
| Worked 2 or more gross hours | Returns 0 |
| Hard-coded — T&H on Saturday is always max 2 hrs | — |
Day shift condition | Start between 06:30–18:30 | Multiplies by 0 — night shift Saturday goes to Double |
Result when all conditions pass: T&H = 2 hours (fixed). Remaining Saturday hours go to Double Time.
Expected Results
Day | Start time | Hours | Time & Half |
Wednesday (day shift) | 07:00 | 9.5 net | 1.5 |
Wednesday (day shift) | 07:00 | 10.5 net | 2.0 (capped) |
Thursday (night shift) | 20:00 | 7.5 net | 0 |
Saturday (day shift) | 07:00 | 1.5 gross | 1.5 |
Saturday (day shift) | 07:00 | 6.0 gross | 2.0 (capped) |
Saturday (night shift) | 21:00 | 6.0 | 0 |
Sunday | 08:00 | 5.5 net | 0 |