minnal_guy2002
New Member
- Joined
- Feb 16, 2025
- Messages
- 2
- Office Version
- 2016
- Platform
- Windows
Dear Expert,
I am finding some difficulties in get the data, need help to resolve this issue.
If Clock In (I2) < 8:00 AM, start counting from 8:00 AM.
If Clock In ≥ 8:00 AM, use the actual Clock In time.
If Clock Out (J2) > 8:00 PM, start counting 8:00 PM.
If Clock Out 8:00 PM, use the actual Clock Out time.
Subtract Clock Out (J2) and Break Time (2 hours).
I am finding some difficulties in get the data, need help to resolve this issue.
If Clock In (I2) < 8:00 AM, start counting from 8:00 AM.
If Clock In ≥ 8:00 AM, use the actual Clock In time.
If Clock Out (J2) > 8:00 PM, start counting 8:00 PM.
If Clock Out 8:00 PM, use the actual Clock Out time.
Subtract Clock Out (J2) and Break Time (2 hours).
OP&Outlet OT (Test).xlsx | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
1 | No | Emp ID | Emp Name | BRANCH | DEPT | POSITION | AREA | SECTION | Clock In | Clock Out | Breaks 2x | Total Hrs | Regular Hrs | OT Hrs | ||
2 | 1 | FWAC0002 | ROHMOTULLAH | KKSM | SIM2-JALAN SULTAN ISMAIL 2(KL) | SHOP ASST | G1-0001 | 0535-SIM2 | 7:30 | 21:30 | 2.00 | 11:30 | 7:30 | 4:00 | ||
3 | 2 | FWAC0094 | MD JUNAED MIA | KKSM | SIM2-JALAN SULTAN ISMAIL 2(KL) | SHOP ASST | G1-0001 | 0535-SIM2 | 8:23 | 17:32 | 2hrs | 7:09 | 7:09 | 0:00 | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
L2 | L2 | =IF(OR(I2="", J2=""), "", (J2 - MAX(I2, TIME(8,0,0))) - (2/24)) |
M2 | M2 | =IF($I$2="","",MIN(7.5/24,MIN(20/24,$J$2)-MAX(7.5/24,I2)-(2/24))) |
N2 | N2 | =IF($I$2="","",$L$2-$M$2) |
L3 | L3 | =IF($I$3="","",$J$3-$I$3-(2/24)) |
M3 | M3 | =IF($I$3="","",MIN(7.5/24,MIN(20/24,$J$3)-MAX(7.5/24,I3)-(2/24))) |