I am trying to calculate the working hours for employees that work in the night shift.

However, the structure and the timing of the shift is making it extremely hard on me to figure it out since the Check in/out data is recorded on two different dates.

For example,

John is a night shift employee that starts working at 19:00 PM (25/11/2021) and his shift ends the next day at 2:00 AM (26/11/2021).

Lets say John didn't show up for work on the 26th.

The attendance device will put the clock-out value (D2) in C2 and the same thing will happen when John Clock-in on the 27th ( the Attendance device would put C3 in D3)

Thus is messing up the data, and the formula that i am using to calculate the working hours. which is

     MOD( $C (X 1) - $D X ,1)

What's recorded on the record sheet

The question is.

1. Is there a better way to calculate the working time (with two dates)since the current mod formula isn't optimal in my newbie opinion?

In addition, it's still calculating hours for the employee even though the employee is absent.

CodePudding user response：

• Add a day, if Clock-out D2 is less than Clock-in C2

• E2:

  =IF(D2<C2,D2 1,D2)-C2
  

• Or E1(Arrayformula):

  ={"Working Time";ARRAY_CONSTRAIN(ARRAYFORMULA(IF(D2:D<C2:C,D2:D 1,D2:D)-C2:C),COUNT(D2:D),1)}