Xin giúp em tính thời gian đi muộn, về sớm (1 người xem)

[kieuqtoan]

Đây là bảng chấm công của công ty em, em muôn tính toán xem trong ngày hôm đó người đó đi muộn, về sớm bao nhiêu phút. Em đã nghiên cứu nhưng có một vài hôm người ta check 2 lần nên bị loạn và nhiều hôm người ta không check (cũng bị loạn). Rất mong được anh chị giúp đỡ, em chân thành cảm ơn!

Công ty em làm việc từ 8h, nghỉ lúc 17h
riêng thứ 7 thì làm từ 8h30, nghỉ lúc 12h
Định dạng của máy chấm công sau mỗi lần quét vân tay:

4/17/2014 8:01:19 AM
4/17/2014 4:46:13 PM

 

[leonguyenz]

Đây là bảng chấm công của công ty em, em muôn tính toán xem trong ngày hôm đó người đó đi muộn, về sớm bao nhiêu phút. Em đã nghiên cứu nhưng có một vài hôm người ta check 2 lần nên bị loạn và nhiều hôm người ta không check (cũng bị loạn). Rất mong được anh chị giúp đỡ, em chân thành cảm ơn!
Công ty em làm việc từ 8h, nghỉ lúc 17h
riêng thứ 7 thì làm từ 8h30, nghỉ lúc 12h
Định dạng của máy chấm công sau mỗi lần quét vân tay:
4/17/2014 8:01:19 AM
4/17/2014 4:46:13 PM

Dùng 4 cột phụ để xử lý:
Tách ngày, I2:

=DATE(--MID(D2,FIND("/",D2,4)+1,4),--LEFT(D2,FIND("/",D2)-1),--MID(D2,FIND("/",D2)+1,FIND("/",D2,4)-FIND("/",D2)-1))

Tách giờ, J2:

=TIMEVALUE(MID(D2,FIND(" ",D2)+1,255))

Xác định thời gian Ra/Vào và loại bỏ vân tay trùng, K2:

=IF(AND(SUMPRODUCT(($B$2:$B$885=B2)*($I$2:$I$885=I2))=1,RIGHT(D2,2)="AM"),"Vào",IF(AND(SUMPRODUCT(($B$2:$B$885=B2)*($I$2:$I$885=I2))=1,RIGHT(D2,2)="PM"),"Ra",IF(SUMPRODUCT(($B$2:$B2=B2)*($I$2:$I2=I2))=1,"Vào",IF(SUMPRODUCT(($B$2:$B2=B2)*($I$2:$I2=I2))=SUMPRODUCT(($B$2:$B$885=B2)*($I$2:$I$885=I2)),"Ra",""))))

Số giờ đi trễ về sớm, L2:

=IF(OR(K2="",AND(K2="Vào",RIGHT(D2,2)="PM"),AND(K2="Ra",RIGHT(D2,2)="AM")),0,IF(K2="Vào",MAX(0,J2-IF(WEEKDAY(I2)=7,8.5/24,8/24)),MAX(0,17/24-J2)))

Và 2 cột Tổng hợp kết quả:
Trích lọc duy nhất tên nhân viên, N2:

=IF(ISNA(INDEX($B$2:$B$885,MATCH(1,INDEX((COUNTIF($N$1:N1,$B$2:$B$885)=0)*($B$2:$B$885<>""),0),0))),"",INDEX($B$2:$B$885,MATCH(1,INDEX((COUNTIF($N$1:N1,$B$2:$B$885)=0)*($B$2:$B$885<>""),0),0)))

Tổng giờ đi trễ về sớm, O2:

=IF(N2="","",SUMIF($B$2:$B$885,N2,$L$2:$L$885))

 

[Let'GâuGâu]

Đây là bảng chấm công của công ty em, em muôn tính toán xem trong ngày hôm đó người đó đi muộn, về sớm bao nhiêu phút. Em đã nghiên cứu nhưng có một vài hôm người ta check 2 lần nên bị loạn và nhiều hôm người ta không check (cũng bị loạn). Rất mong được anh chị giúp đỡ, em chân thành cảm ơn!

Công ty em làm việc từ 8h, nghỉ lúc 17h
riêng thứ 7 thì làm từ 8h30, nghỉ lúc 12h
Định dạng của máy chấm công sau mỗi lần quét vân tay:

4/17/2014 8:01:19 AM
4/17/2014 4:46:13 PM

góp thêm một cách nữa
dùng cột phụ để tính thời gian đi sớm về trể.
(với quy đinh là trước 11 giờ thì xem là giờ vào, sau 11 giờ xem là giờ ra, vì vậy có ai có trường hợp vào 11 giờ 1 p về 17 giờ thì phải sữa lại đk).
sau đó dùng pivot table thống kê lại

 

[kieuqtoan]

Dùng 4 cột phụ để xử lý:
Tách ngày, I2:

=DATE(--MID(D2,FIND("/",D2,4)+1,4),--LEFT(D2,FIND("/",D2)-1),--MID(D2,FIND("/",D2)+1,FIND("/",D2,4)-FIND("/",D2)-1))

Tách giờ, J2:

=TIMEVALUE(MID(D2,FIND(" ",D2)+1,255))

Xác định thời gian Ra/Vào và loại bỏ vân tay trùng, K2:

=IF(AND(SUMPRODUCT(($B$2:$B$885=B2)*($I$2:$I$885=I2))=1,RIGHT(D2,2)="AM"),"Vào",IF(AND(SUMPRODUCT(($B$2:$B$885=B2)*($I$2:$I$885=I2))=1,RIGHT(D2,2)="PM"),"Ra",IF(SUMPRODUCT(($B$2:$B2=B2)*($I$2:$I2=I2))=1,"Vào",IF(SUMPRODUCT(($B$2:$B2=B2)*($I$2:$I2=I2))=SUMPRODUCT(($B$2:$B$885=B2)*($I$2:$I$885=I2)),"Ra",""))))

Số giờ đi trễ về sớm, L2:

=IF(OR(K2="",AND(K2="Vào",RIGHT(D2,2)="PM"),AND(K2="Ra",RIGHT(D2,2)="AM")),0,IF(K2="Vào",MAX(0,J2-IF(WEEKDAY(I2)=7,8.5/24,8/24)),MAX(0,17/24-J2)))

Và 2 cột Tổng hợp kết quả:
Trích lọc duy nhất tên nhân viên, N2:

=IF(ISNA(INDEX($B$2:$B$885,MATCH(1,INDEX((COUNTIF($N$1:N1,$B$2:$B$885)=0)*($B$2:$B$885<>""),0),0))),"",INDEX($B$2:$B$885,MATCH(1,INDEX((COUNTIF($N$1:N1,$B$2:$B$885)=0)*($B$2:$B$885<>""),0),0)))

Tổng giờ đi trễ về sớm, O2:

=IF(N2="","",SUMIF($B$2:$B$885,N2,$L$2:$L$885))

Rất cảm ơn anh, em có chút thắc mắc:
Em tách giờ bằng:

=RIGHT(D658,11)

Tách ngày bằng:

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

Tách ra vào bằng:

=IF(K658=K659,"Vào","Ra")

Trước khi hỏi em cũng chỉ tính toán được có đến đây

 

[kieuqtoan]

góp thêm một cách nữa
dùng cột phụ để tính thời gian đi sớm về trể.
(với quy đinh là trước 11 giờ thì xem là giờ vào, sau 11 giờ xem là giờ ra, vì vậy có ai có trường hợp vào 11 giờ 1 p về 17 giờ thì phải sữa lại đk).
sau đó dùng pivot table thống kê lại

Em thấy cột I của anh một số chỗ nó hiện thị số âm

 

[Let'GâuGâu]

Em thấy cột I của anh một số chỗ nó hiện thị số âm

thì số dương là đi sớm về muộn (hy sinh cho cty)
số âm là đi trể về sớm (cty hy sinh cho minh)
ko phải ý đồ bạn là vậy sao?

 

[ptm0412]

Tách giờ, J2:

=TIMEVALUE(MID(D2,FIND(" ",D2)+1,255))

Tách ngày chỉ cần:
=DATE(2014;4;IFERROR(MONTH(INT(D2));MID(D2;3;2)))

Tách giờ chỉ cần
=1*MID(D2;FIND(" ";D2)+1;12)

 

[ytho]

chào bạn, bên mình có bảng giờ ra và giờ vào theo máy chấm công giờ mình muốn tính toán số phút đi trể bao nhiêu? số phút đi sớm bao nhiêu? cty minh làm vào làm 7h30 phút và về lúc 17h ạ. Rất mong được anh chị giúp đỡ, em chân thành cảm ơn!

 

[mienthuy]

chào bạn! bảng dự liệu chấm công có các quy định đính kèm như sau: nay em muốn trừ thời gian đ trể, về sớm của người lao động (phút thứ 6 trở đi bắt đầu trừ đi muộn)
Rất mong được anh chị giúp đỡ, em chân thành cảm ơn!

File đính kèm