# Need Help Calculating Total Hours in Timesheet

1. ## 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.

2. ## Re: Need Help Calculating Total Hours in Timesheet

Just use helper columns and subtract start from stop for each segment:
``Please Login or Register  to view this content.``
Hide the helper columns

3. ## Re: Need Help Calculating Total Hours in Timesheet

Try this in L2 and copy down:

Formula:
`Please Login or Register  to view this content.`

There are currently 1 users browsing this thread. (0 members and 1 guests)