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

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

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

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])
``````

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?

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"))
``````