Calculate total hours worked in a custom print template by subtracting a start time from a finish time and accounting for a break duration. Standard math operators cannot do this correctly because times are stored as text strings, not numbers.
|
Before you start
|
Why basic math does not work for time
When a worker enters 6:00 am as a start time and 6:30 pm as a finish time, Assignar stores these as text strings: "06:00" and "18:30". If you use a simple subtraction in your template:
|
This looks correct but gives the wrong answer: {$_row.data.id6 - $_row.data.id5 - $_row.data.id7}{/tablerow} Result: 18 - 6 - 0 = 12 (ignores minutes completely) 6:30pm minus 6:00am should be 12.5 hours, not 12 |
Custom print math treats the time values as numbers and ignores the colon and everything after it. 18:30 - 6:00 = 12, not 12.5 or 12:30. To get the correct result, you must extract the hours and minutes separately using substr, convert everything to minutes, do the math, then convert back to hours.
Example form setup
This example uses a group question containing four fields:
- Personnel Name ({$_row.data.id3}) — Worker field
- Start ({$_row.data.id5}) — Time field (stored as HH:MM)
- Finish ({$_row.data.id6}) — Time field (stored as HH:MM)
- Break in Mins ({$_row.data.id7}) — Number field (stored in minutes)
The form fields as seen by the worker: Start 06:00 am, Finish 06:30 pm, Break 30 mins.
The custom print template has a fifth column, Total Hours (calculated), which will hold the formula:
The custom print template table with Personnel Name, Start, Finish, Break (mins), and Total Hours (calculated) columns. The Total Hours cell holds the formula code.
The formula
Use this code in the Total Hours (calculated) cell of your template. Replace the field codes with the codes from your own form's Custom Printing panel.
|
{((($_row.data.id6|substr:0:2)*60)+($_row.data.id6|substr:3:5) -(($_row.data.id5|substr:0:2)*60)-($_row.data.id5|substr:3:5) -$_row.data.id7)/60}{/tablerow} id6 = Finish time field id5 = Start time field id7 = Break duration field (in minutes) |
How the formula works
Time fields are stored as 5-character strings in HH:MM format (e.g. "18:30" for 6:30 pm). The substr function extracts specific characters from a string by position. The formula converts everything to minutes, does the math, then divides back to hours.
| Code fragment | What it does | Value (example: 6:00am start, 6:30pm finish, 30 min break) |
|---|---|---|
| ($_row.data.id6|substr:0:2)*60 | Extract the finish hours (characters 0-1) and convert to minutes | 18 hours x 60 = 1080 mins |
| +($_row.data.id6|substr:3:5) | Add the finish minutes (characters 3-4) | 1080 + 30 = 1110 mins |
| -($_row.data.id5|substr:0:2)*60 | Extract the start hours and convert to minutes | 1110 - 360 = 750 mins |
| -($_row.data.id5|substr:3:5) | Subtract the start minutes | 750 - 0 = 750 mins |
| -$_row.data.id7 | Subtract the break duration (already in minutes) | 750 - 30 = 720 mins |
| /60 | Divide total minutes by 60 to get hours | 720 / 60 = 12 hours |
|
How substr works |substr:0:2 starts at character position 0, reads 2 characters. From "18:30" this gives "18" (the hours). |substr:3:5 starts at character position 3, reads 2 characters. From "18:30" this gives "30" (the minutes). Character positions in "18:30": 1=0, 8=1, :=2, 3=3, 0=4. The colon is at position 2, so minutes always start at position 3. |
Worked example: Start 06:00 am, Finish 06:30 pm, Break 30 mins
|
Why the formula does not use simple subtraction Adding {$_row.data.id6 - $_row.data.id5 - $_row.data.id7}{/tablerow} as the Total Hours cell code gives inaccurate values. Basic math in custom printing does not account for timestamps. Subtracting Start from Finish only compares the hours digits, ignoring minutes completely. This makes 18:30 - 6:00 = 12, not 12.5 or 12:30. |
Corrected code:
|
{((($_row.data.id6|substr:0:2)*60)+($_row.data.id6|substr:3:5) -(($_row.data.id5|substr:0:2)*60)+($_row.data.id5|substr:3:5) -$_row.data.id7)/60}{/tablerow} |
Step-by-step explanation
(($_row.data.id6|substr:0:2)*60) Explained:
We use "substr" (substring) to pull out specific values from the text string from one point for a set duration. In this case, for "18:30" (how 6:30 pm Finish time is stored) there are 5 characters, but the array is stored 0-4 rather than 1-5.
We pull out the hours by using "|substr:0:2" to give us characters starting at 0, then for the length of 2 characters: "18".
The *60 converts these hours to minutes so we can do math with the break, which is stored in minutes.
This gives us 1080.
+($_row.data.id6|substr:3:5) Explained:
The next part adds the minutes, which is the same substr calculation but starting at character array point 3 (the fourth place, or "3" of "18:30").
This gives us 1080+30.
(($_row.data.id5|substr:0:2)*60)+($_row.data.id5|substr:3:5)-$_row.data.id7 Explained:
We apply the same logic to the Start time field (id5) to extract its hours and minutes, then subtract them from the running total.
We then subtract the Break duration (id7), which is already in minutes.
Leaves us with values: 1080+30-360+0
We then subtract the Break Time for 1080+30-360+0-30. (Total Time = 720)
/60}{/tablerow} Explained:
We divide this value by /60 to convert the minutes back into hours.
Total Time = 12
Final code for Total Hours (calculated):
| {((($_row.data.id6|substr:0:2)*60)+($_row.data.id6|substr:3:5)-(($_row.data.id5|substr:0:2)*60)+($_row.data.id5|substr:3:5)-$_row.data.id7)/60}{/tablerow} |
Things to know
- Time fields must be stored in HH:MM format (24-hour) for the substr positions to work correctly. Check your form's time field format before using this formula.
- The break field must be in minutes (a number field), not a time field. If your break is stored differently, adjust the formula accordingly.
- This formula calculates decimal hours. 12.5 means 12 hours and 30 minutes. If you need HH:MM output, additional formatting is required.
- Replace id5, id6, and id7 with the codes shown in your form's Custom Printing panel. These IDs are unique to each form.
- The formula only works correctly inside a tablerow loop. The {/tablerow} closing tag must be at the end of the formula as shown.