Tính thời gian làm việc máy chấm công (1 người xem)

[dunglx911]

Mình có một vấn đề nhờ các bạn giúp đỡ, bên phía cty mình mới dùng máy chấm công, nó xuất ra dữ liệu như sau:

Vào:
A5 1/9/2014 7:07:00 AM
A6 : 1/9/2014 21:50:00 PM

Ra :
B5 1/9/2014 17:09:00 PM
B6: 2/9/2014 6:05:00 AM

Mình muốn tạo công thức để tính được thời gian làm việc, có thể hiện thị là 8,1h hay là 08:10 củng được

Mong các cao thủ chỉ giúp,
Nếu bạn nào đả gặp TH này thì cho mình xin file tính công luôn nhé

Cảm ơn các bạn,

 

[SA_DQ]

Thường mình thấy loại dữ liệu này dạng chuỗi; Cần viết hàm ngươì dùng mới chuyển ra dạng như bạn iêu cầu;

Tốt nhất bạn đưa file giả lập lên hay đưa đường dẫn đến 1 file nào đã sẵn trên diễn đàn để dễ bề trao đổi.

.

 

[dunglx911]

Thank SA_DQ, mình gửi file chấm công của mình lên nhé

​

​

 

[sealand]

Thường mình thấy loại dữ liệu này dạng chuỗi; Cần viết hàm ngươì dùng mới chuyển ra dạng như bạn iêu cầu;

Tốt nhất bạn đưa file giả lập lên hay đưa đường dẫn đến 1 file nào đã sẵn trên diễn đàn để dễ bề trao đổi.

Các file của bác bị dính StartUp, bác sử lý lại giùm.

 

[Vu Dinh Duy]

Thank SA_DQ, mình gửi file chấm công của mình lên nhé

​

Bạn xem thử file đính kèm xem đúng chưa nhé.

 

[sealand]

Bạn phải nêu rõ quy cách xác định công của công ty bạn quy định:

1/Có trừ thời gian giữa các lần quẹt thẻ ra hay không?
2/Những ô trống được sử lý như thế nào? 1 số có vào không thấy ra, 1 số có ra nhưng thấy vào? Trống cả vào và ra?
3/Những dòng không có thông tin người quẹt thẻ có phải là người ở dòng trên không?

Còn trước mắt mình tham gia nên viết 2 Hàm UDF cực nhỏ như sau để chuyển đổi chuỗi sang ngày giờ, xác định thời gian giữa 2 chuỗi ngày giờ:

Option Explicit

Function ConvTime(ByVal St As String)
'Chuyen chuoi dang ngay gio sang ngay gio
On Error Resume Next
ConvTime = CDate(St)
If Err.Number <> 0 Then ConvTime = ""
End Function

'----------------------------------

Function DifTime(St1 As String, St2 As String)
'Xac dinh thoi gian giua hai lan quet the
On Error Resume Next
DifTime = CDate(St2) - CDate(St1)
If Err.Number <> 0 Then DifTime = ""
End Function

Từ kết quả này bạn hoàn toàn có thể tính toán được
Bạn lưu ý format cột kết quả là "#,##0" hoặc "hh:mm" để thể hiện đúng ý bạn nha

 

[ec ec]

thank sa_dq, mình gửi file chấm công của mình lên nhé

​

w12=24*(substitute(r12,"pm","")-substitute(m12,"pm",""))

 

[ChanhTQ@]

Mình xài công thức của bạn Ec ec thì chỉ ô đó là có số liệu thôi, các ô dưới nó đều sai là sao vậy?

 

[dunglx911]

Cảm ơn bạn Sealand và vu dinh Duy, các bạn rất tuyệt
Mình sẻ dùng file của bạn Vu Dinh Duy vì không dùng macro
Thank. all

 

[Vu Dinh Duy]

Cảm ơn bạn Sealand và vu dinh Duy, các bạn rất tuyệt
Mình sẻ dùng file của bạn Vu Dinh Duy vì không dùng macro
Thank. all

File của mình có thể sửa lại công thức tính ra giờ có số lẻ cho gọn hơn nhé:
=(LEFT(R12,LEN(R12)-3)-LEFT(M12,LEN(M12)-3))*24

 

[sealand]

Đối với trường hợp máy chấm công của cơ quan bạn Format dữ liệu không theo dạng quy chuẩn thế này (đã 13 giờ lại còn PM) làm cho công thức có thể sai lệch. Bảng lương của 1 Cty lớn mà sai sót thì có ngày đền không thấu.
Để đảm bảo việc tin cậy, mình hướng dẫn bạn dùng phương pháp Text to Column như sau:

1/Xoá hết phần tiêu đề từ dòng 1 đến dòng 8 cho bớt lằng nhằng.

2/Nhấn tiêu đề cột M chọn cả cột M.
-Vào Data -- Text to Columns....
-Chọn Delimited ---Nhấn Next
-Phần Delimiters bỏ hết chọn 1 Space thôi---Nhấn Next
-Tại Destination: Nhập vào "$W#1"---Finish

Ta được 3 cột kết quả, ta xoá bỏ cột thứ 3 (AM-PM)
Do cột ngày tháng định dạng dd/mm/yyyy dạng Text nên máy hiểu sai, ta dùng cột Y để làm cột ngày tháng Đ/c. Tại Y4 bạn nhập công thức và filldown xuống.

=IF(ISNUMBER(W4),DATE(YEAR(W4),DAY(W4),MONTH(W4)),"")

3/Tương tự với cột ra ta được 3 cột là Z, AA, AB

4/Tại cột AC bạn nhập công thức sau rồi Filldown ta được giờ công:

=IF(AB4=Y4,AA4-X4,IF(AND(X4>0,AA4>0),(AB4-Y4)*24-X4+AA4,""))

Với cách này bạn khỏi lo ngày vào, tháng vào, năm vào khác ngày ra, tháng ra, năm ra.

Bạn tham khảo kết quả tôi làm nha.