Chia sẻ công thức Excel tính giờ làm việc hành chính trừ giờ nghỉ

 Trong thực tế tính công theo giờ công thì không đơn thuần lấy giờ ra trừ đi giờ vào để ra thời gian chấm công, mà còn phụ thuộc vào các yếu tố khác như:

- Đến sớm hơn giờ vào quy định thì lấy theo giờ vào quy định. Ví dụ: Giờ IN quy định là 8:00 mà nhân sự đến từ lúc 7:45 thì vẫn tính từ lúc 8:00

- Về muộn hơn giờ ra quy định thì vẫn lấy theo giờ ra quy định. Ví dụ: Giờ OUT quy định là 17:00 mà nhân sự về lúc 17:45 thì giờ hành chính vẫn tính đến 17:00 (sau đó là tính thời gian tăng ca riêng)

- Nghỉ giữa giờ 1 tiếng. Ví dụ từ 12:00 đến 13:00. Thì sẽ phải loại 1 tiếng nghỉ trưa này đi. Tuy nhiên có phát sinh tình huống  nhân sự nghỉ buổi sáng hoặc nghỉ buổi chiều thì không xét trừ 1 tiếng nghỉ trưa

Hướng dẫn lập công thức xử lý theo hình ảnh dưới đây:

Lập công thức quy về giờ vào (IN) chuẩn: Công thức tại cột D (ô D3)

- Xét tình huống vào trước và sau 8:00

  *** Công thức: =MAX(--B3,TIME(8,0,0))

  *** Diễn giải công thức: Nếu >8:00 thì lấy giờ out thực tế, nếu <8:00 thì lấy đúng 8:00

- Xét tiếp tình huống vào trước và vào sau: 13:00

  *** Công thức xét cả vào trước sau 8:00 và trước sau 13:00 : 

=IF(--B3>=TIME(13,0,0),--B3,IF(--B3>TIME(12,0,0),TIME(13,0,0),MAX(--B3,TIME(8,0,0))))

  *** Diễn giải công thức Nếu >13:00 thì lấy từ giờ out thực tế, nếu <13:00 và lớn hơn 12:00 thì lấy 13h, còn lại thì lấy theo giờ vào theo công thức xét ở tình huống 8:00 ở trên

=> Như vậy công thức xử lý triệt để giờ IN là:

=IF(--B3>=TIME(13,0,0),--B3,IF(--B3>TIME(12,0,0),TIME(13,0,0),MAX(--B3,TIME(8,0,0))))

Lập công thức quy về giờ ra (OUT) chuẩn (theo công hành chính): Công thức tại ô E3

- Xét tình huống về trước và sau 17:00

  *** Công thức: =MIN(--C3,TIME(17,0,0))

  *** Diễn giải công thức: Nếu >17:00 thì lấy 17:00, ngược lại thì lấy đúng giờ ra thực tế

- Xét tiếp tình huống ra trước và ra sau 12:00

  *** Công thức: =IF(--C3<TIME(12,0,0),--C3,IF(--C3<TIME(13,0,0),TIME(12,0,0),MIN(--C3,TIME(17,0,0))))

  *** Diễn giải công thức: Nếu ra trước 12h thì lấy đúng giờ ra thực tế, nếu ra >12:00 và <13:00 thì xét ra lúc 12:00, còn lại lấy theo tình huống ra trước sau 17:00

=> Như vậy công thức xử lý triệt để giờ OUT là: 

=IF(--C3<TIME(12,0,0),--C3,IF(--C3<TIME(13,0,0),TIME(12,0,0),MIN(--C3,TIME(17,0,0))))

Lập công thức tính giờ công hành chính

Qua 2 bước lập công thức xét giờ IN và giờ OUT thì ta sẽ được kết quả như sau:

- Nếu giờ OUT quy đổi - IN quy đổi<=4 thì không trừ 1 tiếng nghỉ trưa

- Nếu giờ OUT quy đổi - IN quy đổi>4 thì trừ 1 tiếng nghỉ trưa

=> Như vậy công thức tính giờ công hành chính là: 

=IF(E3-D3<4,E3-D3,E3-D3-1)*24

Ngoài ra, nếu trường hợp quên chấm công vào hoặc quên chấm công ra thì người dùng phải sửa tay dữ liệu chấm công hoặc lồng hàm IF xét ô trống

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334