# Sum date/time cells, in a work schedule, to ignore text AND still subtract a break time

#### jparks1015

##### New Member
I used the formula below in a monthly work schedule I am creating. The schedule reads (Time Out - Time In) - (Lunch out - Lunch in) to give me a total number hours worked for each employee, minus their 30 minute lunch break. The schedule is on one tab and the data is on another. I was originally getting the ### error if cells were blank when the employee was not working that day. In my data sheet, I have the total hours per day, separated out. When I sum all the hours worked for that person, that month, It looked like the sum was correct. I was getting a number close but it was not correct. It was deducting 30 minutes from each cell that had the error.

=(DECEMBER!N8-DECEMBER!M8)-(DECEMBER!\$AB\$7-DECEMBER!\$AA\$7)

I then used this formula (below) which corrected the ### error and sums correctly however, it does not subtract the lunch break.

=IF(AND(ISNUMBER(DECEMBER!K6),ISNUMBER(DECEMBER!J6)),DECEMBER!K6-DECEMBER!J6,"")

I need to know how to adapt the new formula to also subtract the lunch break.

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### NiMip

##### Board Regular
Re: How do I sum date/time cells, in a work schedule, to ignore text AND still subtract a break time

Hi there, can you paste some example data so I can see what your references relate to for the second equation? Your first equation presumably follows what you wrote before "(Time Out - Time In) - (Lunch out - Lunch in)", but the second one is referencing different cells?

#### jparks1015

##### New Member
Re: How do I sum date/time cells, in a work schedule, to ignore text AND still subtract a break time

Hi there, can you paste some example data so I can see what your references relate to for the second equation? Your first equation presumably follows what you wrote before "(Time Out - Time In) - (Lunch out - Lunch in)", but the second one is referencing different cells?

RE: I copied the formulas from different cells. The first formula is referencing time in/time out - lunch in/lunch out. The second formula references time in/time out (the same as the first only it was on a different employee) so the K6 & J6 are the same as the N8 & M8, it's just a different employee. The lunch in/out is AB7 & AA7 and is not shown in the second formula because I don't know how to add it. I hope this answer is what you need.

A B C D E F G ....................... AA AB
4 EMP 1 EMP 2 EMP 3...
5 date day blank time in time out time in time out Lunch in Lunch out
6
7
8
9

#### jparks1015

##### New Member
Re: How do I sum date/time cells, in a work schedule, to ignore text AND still subtract a break time

I got it!!!

=if(and(isnumber(december!k6),isnumber(december!j6)),(december!k6-december!j6)-(december!\$ab\$5-december!\$aa\$5),"")

#### NiMip

##### Board Regular
Re: How do I sum date/time cells, in a work schedule, to ignore text AND still subtract a break time

Nice one Always very satisfying to figure it out yourself!

#### jparks1015

##### New Member
Re: How do I sum date/time cells, in a work schedule, to ignore text AND still subtract a break time

Yes it was! Thanks for your help though!

Replies
1
Views
115
Replies
3
Views
1K
Replies
3
Views
2K
Replies
3
Views
280
Replies
7
Views
91

1,186,159
Messages
5,956,258
Members
438,243
Latest member
FXA

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back