Chia sẻ công thức Excel tách giờ IN và giờ OUT từ dữ liệu chấm nhiều lần trong ngày

 Nhiều công ty sử dụng máy chấm công và dữ liệu chấm công có thể là nhiều lần/1 ngày/1 nhân sự. Khi đó, để xác định được giờ làm hành chính, giờ tăng ca, thời gian làm ngày nghỉ, lễ thì cần xử lý dữ liệu chấm công cho tình huống này

Phương pháp xử lý: 

- Giờ vào nhỏ nhất (sớm nhất) là giờ IN và giờ vào lớn nhất (muộn nhất) là giờ OUT

- Sau khi tách mà giờ IN=Giờ OUT hoặc giờ IN và giờ OUT hơn nhau rất ít thì sẽ là chấm công thiếu

CÔNG THỨC EXCEL TÁCH GIỜ IN OUT TỪ DỮ LIỆU CHẤM NHIỀU LẦN TRONG NGÀY

Giả sử theo quy định thời gian làm việc của công ty từ 8:00 đến 17:00 và nghỉ trưa từ 12:00 đến 13:00

Bước 1: Tách giờ IN, giờ OUT

Công thức Excel tách giờ IN - Công thức tại I2

=AGGREGATE(15,6,C2:H2/(C2:H2<>""),1)

Lưu ý: Format ô kết quả về định dạng hh:mm

Công thứ Excel tách giờ OUT - Công thức tại J2

=AGGREGATE(14,6,C2:H2/(C2:H2<>""),1)

Lưu ý: Format ô kết quả về định dạng hh:mm

Bước 2: Quy chuẩn thời gian giờ IN, OUT

Quy chuẩn giờ IN

- Nếu vào trước 8:00 thì quy về 8:00

- Nếu vào sau 8:00 và trước 12:00 thì tính theo giờ vào thực tế

- Nếu vào sau 12:00 và trước 13:00 thì quy về 13:00

- Nếu vào sau 13:00 thì tính theo giờ vào thực tế

Công thức quy chuẩn giờ IN:

=IF(I2<TIME(8,0,0),TIME(8,0,0),IF(I2<TIME(12,0,0),I2,IF(I2<TIME(13,0,0),TIME(13,0,0),I2)))

Quy chuẩn giờ OUT

- Nếu ra trước 12:00 thì sẽ lấy theo giờ OUT thực tế

- Nếu ra sau 12:00 và trước 13:00 thì quy về 12:00

- Nếu ra sau 13:00 thì lấy theo giờ OUT thực tế

Công thức quy chuẩn giờ OUT:

=IF(J2<TIME(12,0,0),J2,IF(J2<TIME(13,0,0),TIME(12,0,0),J2))

Bước 3: Tính toán theo dữ liệu đã xử lý

- Tính số phút đi muộn: Tham khảo công thức TẠI ĐÂY

- Tính số phút về sớm: Tham khảo công thức TẠI ĐÂY

- Tính thời gian làm giờ hành chính trừ giờ nghỉ: Tham khảo công thức TẠI ĐÂY

- Tính thời gian tăng ca: Tham khảo công thức TẠI ĐÂY

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334