Hey everyone,
If you've ever tried to calculate total hours worked using separate native Time fields (Start Time & Finish Time) while deducting a Break (mins) number field, you know that SmartSuite's standard time functions are a bit restrictive.
If you want to keep your setup clean without forcing your team to use the combined Date/Time fields or the native time-tracking log, here is a workaround I discovered.
This formula forces SmartSuite to parse the underlying text string of the native Time Picker UI, converts it into 24-hour decimal math, subtracts the times, and automatically converts and deducts your break minutes.
I've attached a screenshot as reference for using this in a shift report or timesheet context.
A few quick setup notes for this to work:
[Start Time] and [Finish Time] are native Time fields (the formula automatically handles the backend string formatting and leading zeros from the Time Picker UI).
[Break (mins)] is a standard Number field.
Set your formula output type to Number (with 2 decimal places) to get clean results like 8.5 hours.
The Formula:
IF(OR([Start Time] == "", [Finish Time] == ""), NUMBER(""),
(
(
IF(CONTAINS([Finish Time], "PM"),
NUMBER(REPLACE(REPLACE(LEFT([Finish Time], 2), "12", "0"), ":", "")) + 12,
NUMBER(REPLACE(REPLACE(LEFT([Finish Time], 2), "12", "0"), ":", ""))
)
+ (NUMBER(REPLACE(LEFT(RIGHT([Finish Time], 5), 2), ":", "")) / 60)
)
-
(
IF(CONTAINS([Start Time], "PM"),
NUMBER(REPLACE(REPLACE(LEFT([Start Time], 2), "12", "0"), ":", "")) + 12,
NUMBER(REPLACE(REPLACE(LEFT([Start Time], 2), "12", "0"), ":", ""))
)
+ (NUMBER(REPLACE(LEFT(RIGHT([Start Time], 5), 2), ":", "")) / 60)
)
)
- IF([Break (mins)] > 0, [Break (mins)] / 60, 0)
)