Formula - Calculating Must Clock out time

gd6noob

Board Regular
Joined
Oct 20, 2017
Messages
109
Office Version
  1. 2016
Platform
  1. Windows
Hi guys,


Im trying to figure out how to calculate this.
From the table below in column M, I would like to figure out the Must Clock Out time. From the first "In" time in column E.
  1. If there are "Out" time in column H, K and L, then column M would day "Clocked Out"
  2. If there are "In" times in column I or K, then it would cancel out the Out times in H and K as it would indicate they would still be onsite.
  3. Column D, if the shift starts with N, and there is an In and Out in colums F and G, then column M would say Clocked Out".
  4. If the last column from left to right is "In" that is populated, I would need to calculate all the hours worked (minus the out time, as this is there break) to not exceed 12 hours in a single shift.
  5. The current formula in column M also has a 15min buffer until reaching 12hours.
12 Hour Tool V4.0 (Make Copy).xlsb
ABCDEFGHIJKLM
1Employee IDEmployee NameManagerShiftInOutInOutInOutInOutMust Clock Out By
2NB3C19154:01:00 AM6:05:00 AM4:16:00 PM
3NF6C18304:01:00 AM5:40:00 AM4:16:00 PM
4NB3C19004:04:00 AM5:30:00 AM4:19:00 PM
Current Shift Clock Punches
Cell Formulas
RangeFormula
M2:M4M2=IF(E2 = "","No Clock In Time",IF(F2<>"",E2+TIME(12,30-15,0),E2+TIME(12,0-15,0)))



Thanks in advanced for any help.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Watch MrExcel Video

Forum statistics

Threads
1,130,029
Messages
5,639,623
Members
417,101
Latest member
amoverton2

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