# Total Time calculation for multiple start/end times in one day

##### New Member
I'm trying to modify an existing timesheet that I created to track employee hours worked. I need to add some additional columns in order to calculate time with a large break in between work.

For example: I have an employee that works 7:00 AM to 2:00 PM then will pick up again at 7:00 PM to 2:30 AM....

I can think of 2 options to perform this:
Option 1: Set the initial start and final end time - add a column for total break hours - then subtract the break hours from the total calculated work hours

Option 2: I can add additional columns for 1st shift total and 2nd shift total and add together.

Which option would be better? How do I do the formula so it will work?
Below is the original spreadsheet that I created..
=IF(AND(ISNUMBER(E5),ISNUMBER(D5)),MOD(E5-D5,1),0)*24

 1 A B C D E F G H I 2 Employee Name: Emp #1 Emp #2 3 Day: Date: Start End Total Hours Start End Total Hours 4 Sunday 0 0 5 Monday 1/1/18 Off Off 0 Off Off 0 6 Tuesday 1/2/18 8:00 AM 9:00 AM 1 7:00 AM 6:30 PM 11.5 7 Wednesday 1/3/18 Off Off 0 6:00 AM 10:00 PM 16 8 Thursday 1/4/18 8:15 AM 2:30 PM 6.25 6:00 AM 7:00 PM 13 9 Friday 1/5/18 6:15 AM 2:45 PM 8.5 7:00 AM 9:30 PM 14.5 10 Saturday 1/6/18 0 0 11 Wk1 Weekly Total 15.75 55 12 Sunday 1/7/18 9:00 AM 6:30 PM 9.5 0 13 Monday 1/8/18 6:30 AM 6:15 AM 23.75 7:00 AM 9:30 PM 14.5 14 Tuesday 1/9/18 6:30 AM 7:00 PM 12.5 6:00 AM 8:30 PM 14.5 15 Wednesday 1/10/18 6:30 AM 6:30 PM 12 7:00 AM 11:00 PM 16 16 Thursday 1/11/18 7:00 AM 6:30 PM 11.5 5:00 PM 12:00 AM 7 17 Friday 1/12/18 7:00 AM 7:00 PM 12 8:30 AM 11:30 AM 3 18 Saturday 1/13/18 7:00 AM 8:00 PM 13 0 19 Wk2 Weekly Total 94.25 55 20 Sunday 1/14/18 7:00 AM 8:00 PM 13 0 21 Monday 1/15/18 7:00 AM 6:00 PM 11 7:00 AM 7:00 PM 12 22 Tuesday 1/16/18 7:00 AM 8:00 PM 13 8:00 AM 6:30 PM 10.5 23 Wednesday 1/17/18 8:30 AM 1:00 PM 4.5 4:00 AM 5:00 PM 13 24 Thursday 1/18/18 8:00 AM 6:00 PM 10 7:00 AM 3:00 PM 8 25 Friday 1/19/18 8:00 AM 5:00 PM 9 0 26 Saturday 1/20/18 0 0 27 Wk3 Weekly Total 60.5 43.5

<tbody>
</tbody>

<tbody>
</tbody>

### Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Replies
27
Views
940
Replies
1
Views
38
Replies
0
Views
166
Replies
3
Views
38
Replies
5
Views
69

1,109,341
Messages
5,528,147
Members
409,804
Latest member
aceyus_michael

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
• Auto-Create a monthly Sign in sheet for preschool students
The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
• VBA vlookup multiple results
Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
• Extract values for earliest/latest times
I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...