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

jparks1015

New Member
Joined
Nov 27, 2017
Messages
10
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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

NiMip

Board Regular
Joined
Oct 9, 2017
Messages
167
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
Joined
Nov 27, 2017
Messages
10
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
Joined
Nov 27, 2017
Messages
10
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
Joined
Oct 9, 2017
Messages
167
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
Joined
Nov 27, 2017
Messages
10
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,193
Messages
5,768,770
Members
425,492
Latest member
blueexcel123

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top