Xin giúp em tính thời gian đi muộn, về sớm

kieuqtoan

Thành viên mới
Tham gia ngày
30 Tháng mười một 2013
Bài viết
5
Được thích
0
Điểm
163
Tuổi
28
Đâ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
 

File đính kèm

Lần chỉnh sửa cuối:

leonguyenz

Thành viên mới
Tham gia ngày
2 Tháng tám 2010
Bài viết
4,185
Được thích
7,394
Điểm
560
Nơi ở
#REF
Đâ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:
Mã:
=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:
Mã:
=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:
Mã:
=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:
Mã:
=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:
Mã:
=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:
Mã:
=IF(N2="","",SUMIF($B$2:$B$885,N2,$L$2:$L$885))
 

File đính kèm

Let'GâuGâu

Thành viên mới
Tham gia ngày
12 Tháng chín 2013
Bài viết
3,140
Được thích
2,733
Điểm
560
Tuổi
47
Đâ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
 

File đính kèm

kieuqtoan

Thành viên mới
Tham gia ngày
30 Tháng mười một 2013
Bài viết
5
Được thích
0
Điểm
163
Tuổi
28
Dùng 4 cột phụ để xử lý:
Tách ngày, I2:
Mã:
=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:
Mã:
=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:
Mã:
=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:
Mã:
=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:
Mã:
=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:
Mã:
=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:
Tách ngày bằng:
=LEFT(D658,(FIND(" ",D658,1)-1))
Tách ra vào bằng:
Trước khi hỏi em cũng chỉ tính toán được có đến đây
 

kieuqtoan

Thành viên mới
Tham gia ngày
30 Tháng mười một 2013
Bài viết
5
Được thích
0
Điểm
163
Tuổi
28
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
 

ptm0412

Excel Ordinary Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
9,320
Được thích
28,349
Điểm
1,910
Tuổi
57
Nơi ở
Gò Vấp

ytho

Thành viên mới
Tham gia ngày
20 Tháng sáu 2019
Bài viết
1
Được thích
0
Điểm
13
Tuổi
36
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!
 

File đính kèm

Top Bottom