calculate time included break

[zico8]

hi,
I have datetime in cells A1:D1

A1: 2018-12-03 06:00 (day work start);
A2: 2018-12-03 10:00 (breakfast break start);
A3: 2018-12-03 11:00 (breakfast break stop);
A4: 2018-12-03 15:00 (day work stop);

So normally this is 9hrs work day with 1hr break included. The workers are payed for 8hrs work.


At cells C1, and C2 I would like to add datetime when the worker is absent.

How can I calculate at C3 the correct time the worker should be payed for??


For example:
C1: 2018-12-03 08:00 (start workers absent)
C2: 2018-12-03 12:00 (stop workers absent)
C3 (result): 05:00 (2hrs work before breakfast break plus 3hrs work after it)

I have no idea how to handle it

 

[oldbrewer]

so it is 8 hours pay but if someone starts at 06:10 and finishes on or after 15:00 they are paid for 7 hours 50 minutes ?

 

[oldbrewer]

in your lower example do we assume worker started at 0600 and finished at 1500 ?

 

[zico8]

in your lower example do we assume worker started at 0600 and finished at 1500 ?

correct;

so it is 8 hours pay but if someone starts at 06:10 and finishes on or after 15:00 they are paid for 7 hours 50 minutes ?

Yes,

This is what I am going to start with.
The overtime hours will be entered to the different place and calculated similarly.

 

[zico8]

It can be also by VBA or PQ if somebody can point the way

 

[oldbrewer]

can we assume your workers clock in and out including for meal break and other absences - if so a spreadsheet with in1 out1 in2 out2 in3 out3 in4 out4 will record enough detail. hours worked will be out1-in1, out2-in2 etc etc - a worker who took a standard meal break will not have in3 etc - how do you transfer clock times to excel - do you read a CSV file

 

[zico8]

Thx old brewer

I am not sure if I understand what exactly out and in from 1 to 4 are.
The data are imputed manually to other sheet like absention: from-to

 

[oldbrewer]

ok - I was suggesting you make provision for an employee to leave and return twice more as well as meal break - if they don't the cells are left blank - you are inputting manually so that is ok. If I start at 7 and leave at 8 and return at 8:45, in1 minus out1 will be 0:45

A1 = 07:00 B1= 08:00 C1 = 08:45 through to H1 = 15:07 say

so in1 - out1 =C1-B1

 

[zico8]

Hi,

Still I did not find the solution
Can anybody help me with?

oldbrewer: your lower example did not solve my example from post 1.

 

[Repush]

try:
=(A4-A1)-(MAX(A3,C3)-MIN(A2,C2))