Learn how to use a formula to automatically calculate ordinary hours in a single entry Labour timesheet in Dashpivot Web. This formula caps ordinary hours at 8 per shift and excludes weekends and 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 |
F | Ordinary | 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 Ordinary.
Enter the following formula in the Formula field:
=IF((C1>=Timevalue("06:30AM"))*(C1<Timevalue("06:30PM")), TIMEDIF(C1,D1)-E1, 0) * IF(WEEKDAY(B1,2)<=5, 1, 0) * IF(TIMEDIF(C1,D1)-E1<=8, 1, 0) +IF((C1>=Timevalue("06:30AM"))*(C1<Timevalue("06:30PM")), 8, "0") * IF(WEEKDAY(B1,2)<=5, 1, 0) * IF(TIMEDIF(C1,D1)-E1>8, 1, 0)Save the template.
Understanding the Formula
The formula checks three conditions for each row: whether the shift falls on a weekday, whether the start time falls within day shift hours (06:30–18:30), and how many net hours were worked after break deduction. It has two parts — only one will produce a result per row.
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 day shift, 8 hours or less
IF((C1>=Timevalue("06:30AM"))*(C1<Timevalue("06:30PM")), TIMEDIF(C1,D1)-E1, 0)
* IF(WEEKDAY(B1,2)<=5, 1, 0)
* IF(TIMEDIF(C1,D1)-E1<=8, 1, 0)Component | What it checks | Effect if false |
| Start time is between 06:30 and 18:30 | Returns 0 — no ordinary hours |
| Net hours worked | — |
| It's a weekday (Mon–Fri) | Multiplies result by 0 |
| Net hours are 8 or less | Multiplies result by 0 — switches to Part 2 |
Result when all conditions pass: Ordinary = net hours worked (up to 8).
Part 2 — Weekday day shift, more than 8 hours
IF((C1>=Timevalue("06:30AM"))*(C1<Timevalue("06:30PM")), 8, "0")
* IF(WEEKDAY(B1,2)<=5, 1, 0)
* IF(TIMEDIF(C1,D1)-E1>8, 1, 0)
Component | What it checks | Effect if false |
| Start time is between 06:30 and 18:30 | Returns "0" — no ordinary hours |
| Hard-coded cap — ordinary is always 8 hrs maximum | — |
| It's a weekday (Mon–Fri) | Multiplies result by 0 |
| Net hours exceed 8 | Multiplies result by 0 — uses Part 1 instead |
Result when all conditions pass: Ordinary = 8 hours (capped). Hours beyond 8 carry over to Time & Half.
Expected Results
Day | Start time | Net hours | Ordinary |
Monday | 07:00 | 8.0 | 8.0 |
Wednesday | 07:00 | 10.5 | 8.0 (capped) |
Thursday | 20:00 | 7.5 | 0 (night shift) |
Saturday | 07:00 | 6.0 | 0 (weekend) |
Sunday | 08:00 | 5.5 | 0 (weekend) |