Learn how to use a formula to automatically calculate Double Time hours in a single entry Labour timesheet in Dashpivot Web. Double Time applies to six scenarios: weekday overtime beyond 10 hours, Saturday hours after the first 2, all Sunday hours, weekday early starts (before 06:30), weekday late starts (18:30 or later), and Saturday night shifts.
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 |
H | Double | 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 Double.
Enter the following formula in the Formula field:
=IF(TIMEDIF(C1,D1)-E1>10, TIMEDIF(C1,D1)-10-E1, 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)-2-E1, 0) * IF((C1>=Timevalue("06:30AM"))*(C1<Timevalue("06:30PM")), 1, 0)+IF(WEEKDAY(B1,2)=7, TIMEDIF(C1,D1)-E1, 0)+IF((C1<Timevalue("06:30AM")), TIMEDIF(C1,D1)-E1, 0) * IF(WEEKDAY(B1,2)<=5, 1, 0)+IF(C1>=Timevalue("06:30PM"), TIMEDIF(C1,D1)-E1, 0) * IF(WEEKDAY(B1,2)<=5, 1, 0)+IF(WEEKDAY(B1,2)=6, 1, 0) * IF((C1<Timevalue("06:30AM"))+(C1>=Timevalue("06:30PM")), TIMEDIF(C1,D1)-E1, 0)Save the template.
Understanding the Formula
The formula has six parts, each covering a distinct double-time scenario. 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.
+ as logical OR — in Part 6, two conditions are joined with + rather than *. This means either condition being true is enough to trigger the result.
Part 1 — Weekday day shift overtime beyond 10 hours
IF(TIMEDIF(C1,D1)-E1>10, TIMEDIF(C1,D1)-10-E1, 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 10 | Returns 0 |
| Hours beyond 10 (the Double value) | — |
| Weekday | Multiplies by 0 |
Day shift condition | Start between 06:30–18:30 | Multiplies by 0 |
Result when all conditions pass: Double = net hours − 10. Example: 12 net hours → Double = 2 hours.
Part 2 — Saturday day shift, hours after the first 2
IF(WEEKDAY(B1,2)=6, 1, 0)
* IF(TIMEDIF(C1,D1)>2, TIMEDIF(C1,D1)-2-E1, 0)
* IF((C1>=Timevalue("06:30AM"))*(C1<Timevalue("06:30PM")), 1, 0)Component | What it checks | Effect if false |
| Saturday | Multiplies by 0 |
| Gross hours exceed 2 | Returns 0 |
| Gross hours minus the 2 T&H hours, minus break | — |
Day shift condition | Start between 06:30–18:30 | Multiplies by 0 — night shifts handled by Part 6 |
Result when all conditions pass: Double = total hours − 2 − break. Example: 6 hrs gross, 0.5 break → Double = 3.5 hours.
Part 3 — All Sunday work
IF(WEEKDAY(B1,2)=7, TIMEDIF(C1,D1)-E1, 0)
Component | What it checks | Effect if false |
| It is Sunday | Returns 0 |
| All net hours worked | — |
Result: Every net hour worked on Sunday is Double Time. Start time and hours worked are irrelevant — all Sunday hours are double.
Part 4 — Weekday early start (before 06:30)
IF((C1<Timevalue("06:30AM")), TIMEDIF(C1,D1)-E1, 0)
* IF(WEEKDAY(B1,2)<=5, 1, 0)Component | What it checks | Effect if false |
| Start time is before 06:30 | Returns 0 |
| All net hours worked | — |
| Weekday | Multiplies by 0 |
Result when all conditions pass: All net hours are Double. If a worker starts before 06:30 on a weekday, the entire shift is double time regardless of when it ends.
Part 5 — Weekday late start (18:30 or later)
IF(C1>=Timevalue("06:30PM"), TIMEDIF(C1,D1)-E1, 0)
* IF(WEEKDAY(B1,2)<=5, 1, 0)Component | What it checks | Effect if false |
| Start time is 18:30 or later | Returns 0 |
| All net hours worked | — |
| Weekday | Multiplies by 0 |
Result when all conditions pass: All net hours are Double. This is the mirror of Part 4 — same logic, opposite boundary.
Part 6 — Saturday night shift
IF(WEEKDAY(B1,2)=6, 1, 0)
* IF((C1<Timevalue("06:30AM"))+(C1>=Timevalue("06:30PM")), TIMEDIF(C1,D1)-E1, 0)
Component | What it checks | Effect if false |
| Saturday | Multiplies by 0 |
| Start before 06:30 or at/after 18:30 | Returns 0 |
| All net hours worked | — |
Note: The + between the two time conditions acts as a logical OR — either being true is enough. Saturday night shifts go entirely to Double; no T&H applies.
Result when all conditions pass: All net hours are Double.
Expected Results
Day | Start time | Hours | Double |
Wednesday (day shift) | 07:00 | 12.0 net | 2.0 |
Wednesday (day shift) | 07:00 | 9.5 net | 0 |
Thursday (late start) | 20:00 | 7.5 net | 7.5 |
Thursday (early start) | 05:00 | 8.0 net | 8.0 |
Saturday (day shift) | 07:00 | 6.0 gross, 0.5 break | 3.5 |
Saturday (night shift) | 21:00 | 6.0 net | 6.0 |
Sunday | 08:00 | 5.5 net | 5.5 |