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:


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



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

2 Answers 2


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


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


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


valid Finish time required
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"))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.