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

[jparks1015]

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.​

 

[NiMip]

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]

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]

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]

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]

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!