Need Help Calculating Total Hours in Timesheet

We are trying to calculate the total number of on-duty hours on a time sheet (attached), where several breaks are taken during each shift.

We found a formula that works for the first four columns, but need to expand it to more columns:

=IFERROR(IF(COUNT(Table1[@[Start 1]:[Stop 2]])=4,(IF([@[Stop 2]]<[@[Start 1]],1,0)+[@[Stop 2]])-[@[Start 2]]+[@[Stop 1]]-[@[Start 1]],IF(AND(LEN([@[Start 1]])<>0,LEN([@[Stop 2]])<>0),(IF([@[Stop 2]]<[@[Start 1]],1,0)+[@[Stop 2]])-[@[Start 1]],0))*24,0)

It is important that hours carrying over to the next day also get counted properly (i.e. 11:00pm - 1:30 am).

Any ideas would be appreciated.

Just use helper columns and subtract start from stop for each segment:

Please Login or Register  to view this content.

Hide the helper columns