Hello everyone! First time to post here. 🙂 Been working on this sheet on and off for two weeks now and i just cant get the right formula for this requirement. 

I’m running a store that has at least 10 personnel that is working on different shifts. (Please see below existing template)

As you can see, i have one sheet for multiple personnel. Here is what i wish to accomplish.

1. To get total hours work based on the start of shift time and not on the clocked in by the personnel.

2. Night differential if personnel reaches 10pm to 6am of work

3. Number of late in minutes. Late clock-in from the start of shift and late clock-in from the number of allotted break time.

I’ve filled up the first row just to show the proper figure to display there after computation.

*Shift schedule is 2pm to 12am. That is 10hr shift less the 2 hr break time. hence, 8hr work shift

*Shift ended at 12:16am. Personnel acquires night differential as he worked from 10pm to 12am. hence, 2 hrs ND.

*2pm to 10pm shift starts. Personnel clocked in at 2:09pm. hence, the 9-minute late. He was able to clock-in before allotted break time. so late is just coming from the start of shift.

***Note: Personnel Time-in and out are downloaded from biometrics system. i just copy and paste to excel. 🙂

Hope someone can shed a light if this is something workable in excel.

​Hello everyone! First time to post here. 🙂 Been working on this sheet on and off for two weeks now and i just cant get the right formula for this requirement.  I’m running a store that has at least 10 personnel that is working on different shifts. (Please see below existing template)  As you can see, i have one sheet for multiple personnel. Here is what i wish to accomplish. 1. To get total hours work based on the start of shift time and not on the clocked in by the personnel.2. Night differential if personnel reaches 10pm to 6am of work3. Number of late in minutes. Late clock-in from the start of shift and late clock-in from the number of allotted break time. I’ve filled up the first row just to show the proper figure to display there after computation. *Shift schedule is 2pm to 12am. That is 10hr shift less the 2 hr break time. hence, 8hr work shift *Shift ended at 12:16am. Personnel acquires night differential as he worked from 10pm to 12am. hence, 2 hrs ND. *2pm to 10pm shift starts. Personnel clocked in at 2:09pm. hence, the 9-minute late. He was able to clock-in before allotted break time. so late is just coming from the start of shift. ***Note: Personnel Time-in and out are downloaded from biometrics system. i just copy and paste to excel. 🙂 Hope someone can shed a light if this is something workable in excel.  Read More

​