How to handle negative values when subtracting Start date/time from Finish date/time in a calculated column?

Question

Desired Behaviour

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 Start time).

What I've Tried

I am using SharePoint Online modern list with a calculated column:

=TEXT((Finish-(1/24/60*Breaks))-Start,"h:mm")

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 9:00, 7:00 and 8:30) except it does not handle negative values (it outputs a positive value).

Tests

Good:

// 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

Bad:

// 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.

Question

What formula can be used in a Calculated Column that:

• Displays output in h:mm format
• Returns some sort of error value if Finish time is earlier than Start time (and when Finish time is not defined)
• Returns accurate data if the employee enters shifts that span days.

Answer 1

The following seems to produce desired results, but I am hoping it could be made more succinct.

Entries

// representing Start, Finish and Breaks  

14/06/2020 9:00 AM , 14/06/2020 12:00 AM , 30
14/06/2020 9:00 AM , 14/06/2020 5:00 PM , 30 
14/06/2020 9:00 AM , [ blank ] , [ blank ]

Formula

IF(((Finish-(1/24/60*Breaks))-Start)>0, TEXT((Finish-(1/24/60*Breaks))-Start,"h:mm"), "valid Finish time required")

Output

valid Finish time required
7:30  
valid Finish time required

The logic uses the IF() statement:

IF(logical_test, [value_if_true], [value_if_false])

Sources:
https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2
https://support.microsoft.com/en-us/office/if-function-7025be14-5665-43d0-af20-8293d1fe9d3a

and essentially says:

if (Finish-Start) is greater than 0,
return the TEXT() formatted result,
otherwise, return an error message

It is performing the evaluation twice though (for the [logical_test] and [value_if_true] values).

Can the [logical_test] value be saved as a variable so that it can be used again as the [value_if_true] parameter?

Answer 2

This worked for me when I wanted to calculate days to an Audit with indication if past. I was using this for an automation report:

=IF(ISERROR(DATEDIF(TODAY(),[Audit Date],"d")), -DATEDIF([Audit Date],TODAY(),"d"),DATEDIF(TODAY(),[Audit Date],"d"))