0

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

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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