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

 Ở một số phần mềm chấm công, khi xuất dữ liệu chấm công ra Excel thì ngày tháng và thời gian giờ vào, giờ ra trong cùng 1 cột

Khi đó người phụ trách chấm công cần tách giờ In và giờ OUT riêng

Giả sử tình huống: Công ty chỉ làm hành chính và chỉ chấm công 2 lần/1 ngày. Khi đó sẽ có phát sinh trường hợp chấm công nhiều hơn 2 lần/1 ngày thì giờ vào (giờ IN) sẽ là giờ nhỏ nhất, giờ OUT (giờ ra) sẽ là giờ lớn nhất

CÁC BƯỚC XỬ LÝ DỮ LIỆU VÀ TÁCH GIỜ IN, OUT NHƯ SAU

Bước 1: Tạo côt phụ để tách riêng phần cột ngày và cột thời gian (nếu dữ liệu chấm công xuất ra đã tách cột này rồi thì không cần làm bước này)

- Nếu cột Date/Time là dạng Text thì công thức tách ngày là 

=LEFT(B3,FIND(" ",B3)-1)

và công thức tách thời gian là 

=MID(B3,FIND(" ",B3)+1,99)

- Nếu cột Date/Time là dạng ngày tháng và thời gian chuẩn thì công thức tách ngày là 

=INT(B3)

và công thức tách thời gian là 

=MOD(B3,1)

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

Bước 2: Lấy giờ IN - là giờ nhỏ nhất và chỉ lấy cho dòng đầu tiên của từng người theo từng ngày

=IF(COUNTIFS($A$3:A3,A3,$C$3:C3,C3)=1,AGGREGATE(15,6,$D$3:$D$3286/($A$3:$A$3286=A3)/($C$3:$C$3286=C3),1),"")

Bước 3: Lấy cột giờ OUT - là giờ lớn nhất và chỉ lấy cho dòng đầu tiên của từng người theo từng ngày

=IF(COUNTIFS($A$3:A3,A3,$C$3:C3,C3)=1,AGGREGATE(14,6,$D$3:$D$3286/($A$3:$A$3286=A3)/($C$3:$C$3286=C3),1),"")

Bước 4:Lọc bỏ nhũng dòng IN và OUT trống và làm công thức lấy OUT-IN sẽ ra số giờ làm việc

Trường hợp kết quả OUT-IN=0 tức giờ vào = giờ ra thì nhân sự này ngày hôm đó chỉ chấm 1 lần

Từ kết quả này thì người dùng có thể xử lý tiếp các tính toán khác như: Tính số phút đi muộn, số phút về sớm, số giờ tăng ca, tính số cô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