Calculate hours worked between two date/time columns, subtracting breaks (in minutes) and handling negative values (where a user has incorrectly entered a
Finish time earlier than a
What I've Tried
I am using SharePoint Online modern list with a calculated column:
The logic of the formula is essentially the same as
=TEXT(Finish-Start,"h:mm") (demonstrated in this Microsoft article), but I am subtracting a dynamic amount of minutes from the
Finish time to account for a user's
Breaks throughout the day, per the logic in this article.
This formulate is working as desired (it is outputting values like
8:30) except it does not handle negative values (it outputs a positive value).
// Example A: Finish is later than Start // breaks are subtracted, and daily total is displayed, correctly Start: 28/05/2020 8:45:00 AM Finish: 28/05/2020 4:45:00 PM Breaks: 10 Daily Total: 7:50
// Example B: Finish is earlier than Start // Daily Total shows positive value, instead of negative value Start: 28/05/2020 9:00:00 AM Finish: 28/05/2020 12:00:00 AM Breaks: 30 Daily Total: 9:30
I tried changing the returned type from
Single line of text to
Number (per this answer) but it still displays a positive value.
When I test the formula in Excel, when
Finish is earlier than
Start, it outputs
#VALUE!which is desirable as it shows that something is wrong with the
Finish time entered.
What formula can be used in a Calculated Column that:
- Displays output in
- Returns some sort of
Finishtime is earlier than
Starttime (and when
Finishtime is not defined)
- Returns accurate data if the employee enters shifts that span days.