Tính tổng thời gian theo nhân sự, theo ngày

Liên hệ QC
S

shaming10

Thành viên mới
Tham gia
10/8/18
Bài viết
19
Được thích
5
Chào mọi người,

Nhờ mọi người tư vấn giúp em, em đang muốn tính tổng thời gian nhân sự chọn busy theo ngày, theo người (như file đính kèm), nhờ mọi người tư vấn giúp em hoặc cho em từ khóa gợi ý để tìm hiểu. Em xin phép up file ở đây.

Ví dụ: Nguyễn Văn A, ngày 01/10/2019:
Available_chat: 10:45, busy: 10:50--> thời gian busy: 00:05
Available_chat: 12:45, busy: 13:50--> thời gian busy: 01:05

tổng thời gian busy ngày 01/10/2019 là 01:10

Em cám ơn mọi người.
 

File đính kèm

  • Thoi gian busy.xlsx
    28.4 KB · Đọc: 8
Busy tức là sao? Là đang làm việc khác hay là đang làm việc này?
 
thực tế nên có busy bắt đầu busy kết thúc chứ dựa vào avalible khá là khó hiểu
 
dazkangel đã viết:
thực tế nên có busy bắt đầu busy kết thúc chứ dựa vào avalible khá là khó hiểu
Nhấp chuột vào đây để mở rộng...
Đúng ra thì hết available thì phải là unavailable. Như vậy thì busy nó có cái ngữ cảnh ở giữa avail và unavail. (Người này làm việc online chat)
Ở đây, thớt đặt cho hết avail là busy. Cách này thì giữa avail và busy là tán gẫu, hết tám xong mới bắt tay vào việc.
 
shaming10 đã viết:
Ví dụ: Nguyễn Văn A, ngày 01/10/2019:
Available_chat: 10:45, busy: 10:50--> thời gian busy: 00:05
Available_chat: 12:45, busy: 13:50--> thời gian busy: 01:05
Nhấp chuột vào đây để mở rộng...
  1. Phải điều chỉnh các cột ngày đang là "chuỗi" về dạng"số", với định dạng "dd/mm/yyyy" hoặc "hh:mm dd/mm/yyyy"
  2. Dựa vào ví dụ bạn nêu, coi như 1 nhân viên trong cùng ngày, phân cặp thời gian cứ bắt đầu bằng "Available-chat" thì liền kề dưới phải là "Busy". Nếu phát sinh đơn lẻ hoặc bị xen ngang cách khoảng bằng dữ liệu khác (như "Break_time" chẳng hạn) thì coi như không thỏa.
Nếu đồng ý với điều kiện như vậy thì xem giải pháp trong file kèm.

Thân
 

File đính kèm

  • Thoi gian busy.xlsx
    53.3 KB · Đọc: 12
Lần chỉnh sửa cuối:
VetMini đã viết:
Busy tức là sao? Là đang làm việc khác hay là đang làm việc này?
Nhấp chuột vào đây để mở rộng...
lúc đầu nhân sự vào hệ thống lúc 8h00, chat với khách hàng được 1 lúc, chọn busy để làm việc khác (không chat với khách hàng nữa), khi nào muốn chat với khách hàng thì chọn lại availble (1 ngày nhân sự có thể chọn busy nhiều lần).
Bài đã được tự động gộp:

Phan Thế Hiệp đã viết:
  1. Phải điều chỉnh các cột ngày đang là "chuỗi" về dạng"số", với định dạng "dd/mm/yyyy" hoặc "hh:mm dd/mm/yyyy"
  2. Dựa vào ví dụ bạn nêu, coi như 1 nhân viên trong cùng ngày, phân cặp thời gian cứ bắt đầu bằng "Available-chat" thì liền kề dưới phải là "Busy". Nếu phát sinh đơn lẻ hoặc bị xen ngang cách khoảng bằng dữ liệu khác (như "Break_time" chẳng hạn) thì coi như không thỏa.
Nếu đồng ý với điều kiện như vậy thì xem giải pháp trong file kèm.

Thân
Nhấp chuột vào đây để mở rộng...

Em cám ơn anh Hiệp đã hỗ trợ, anh Hiệp ơi sao em đếm lại bằng tay kết quả bị lệch (của em đếm ra 00:34, công thức đếm ra 00:31- chỉ lấy data tới dòng 14). Anh xem lại giúp em với.
Nếu mở rộng ra lấy thêm thời gian breaktime được không anh?

Capture1.JPG
 
Lần chỉnh sửa cuối:
shaming10 đã viết:
Chào mọi người,

Nhờ mọi người tư vấn giúp em, em đang muốn tính tổng thời gian nhân sự chọn busy theo ngày, theo người (như file đính kèm), nhờ mọi người tư vấn giúp em hoặc cho em từ khóa gợi ý để tìm hiểu. Em xin phép up file ở đây.

Ví dụ: Nguyễn Văn A, ngày 01/10/2019:
Available_chat: 10:45, busy: 10:50--> thời gian busy: 00:05
Available_chat: 12:45, busy: 13:50--> thời gian busy: 01:05

tổng thời gian busy ngày 01/10/2019 là 01:10

Em cám ơn mọi người.
Nhấp chuột vào đây để mở rộng...
Bài nầy dùng công thức rất phức tạp, phải chuyển dữ liệu ngày tháng và ....
Dùng code VBA cũng mệt ...
Mã: 
Sub Available_Busy()
  Dim sArr(), Res(), Dic As Object, iKey$, iKey2$, tmp$
  Dim eRow&, sRow&, i&, iTime As Date, fTime As Date
  With Sheet1
    eRow = .Range("A" & Rows.Count).End(xlUp).Row
    If eRow < 2 Then MsgBox ("Khong co du lieu"): Exit Sub
    sArr = .Range("A2:E" & eRow).Value
  End With

 Set Dic = CreateObject("scripting.dictionary")
    sRow = UBound(sArr)
    ReDim Res(1 To sRow, 1 To 4)
    For i = 1 To sRow
      iKey = sArr(i, 1)
      iKey2 = iKey & sArr(i, 2)
      If sArr(i, 3) Like "Available_Chat" Then
        If Not Dic.Exists(iKey2) Then
          k = k + 1
          Dic.Add iKey2, k
          tmp = sArr(i, 2)
          Res(k, 2) = DateValue(Mid(tmp, 7, 4) & Mid(tmp, 3, 4) & Mid(tmp, 1, 2))
          If Dic.Exists(iKey) = False Then
            Res(k, 1) = iKey
            Dic.Add iKey, ""
          End If
        End If
        ik = Dic.Item(iKey2)
        tmp = sArr(i, 5)
        iTime = TimeValue(Mid(tmp, 1, 5)) + DateValue(Mid(tmp, 13, 4) & Mid(tmp, 9, 4) & Mid(tmp, 7, 2))
        Res(ik, 4) = iTime
      ElseIf sArr(i, 3) Like "Busy" Then
        If Dic.Exists(iKey2) Then
          ik = Dic.Item(iKey2)
          fTime = Res(ik, 4)
          If Len(fTime) Then
            tmp = sArr(i, 5)
            iTime = TimeValue(Mid(tmp, 1, 5)) + DateValue(Mid(tmp, 13, 4) & Mid(tmp, 9, 4) & Mid(tmp, 7, 2))
            Res(ik, 3) = Res(ik, 3) + iTime - fTime
            Res(ik, 4) = Empty
          End If
        End If
      End If
    Next i
  Set Dic = Nothing
  With Sheet1
    eRow = .Range("I" & Rows.Count).End(xlUp).Row
    If eRow > 1 Then .Range("H2:J" & eRow).Clear
    If k Then
      .Range("I2:I2").Resize(k).NumberFormat = "dd/mm/yyyy"
      .Range("J2:J2").Resize(k).NumberFormat = "h:mm"
      .Range("H2:J2").Resize(k) = Res
    End If
  End With
End Sub
 

File đính kèm

  • Thoi gian busy.xlsb
    25.8 KB · Đọc: 7
HieuCD đã viết:
Bài nầy dùng công thức rất phức tạp, phải chuyển dữ liệu ngày tháng và ....
Dùng code VBA cũng mệt ...
Mã: 
Sub Available_Busy()
  Dim sArr(), Res(), Dic As Object, iKey$, iKey2$, tmp$
  Dim eRow&, sRow&, i&, iTime As Date, fTime As Date
  With Sheet1
    eRow = .Range("A" & Rows.Count).End(xlUp).Row
    If eRow < 2 Then MsgBox ("Khong co du lieu"): Exit Sub
    sArr = .Range("A2:E" & eRow).Value
  End With

Set Dic = CreateObject("scripting.dictionary")
    sRow = UBound(sArr)
    ReDim Res(1 To sRow, 1 To 4)
    For i = 1 To sRow
      iKey = sArr(i, 1)
      iKey2 = iKey & sArr(i, 2)
      If sArr(i, 3) Like "Available_Chat" Then
        If Not Dic.Exists(iKey2) Then
          k = k + 1
          Dic.Add iKey2, k
          tmp = sArr(i, 2)
          Res(k, 2) = DateValue(Mid(tmp, 7, 4) & Mid(tmp, 3, 4) & Mid(tmp, 1, 2))
          If Dic.Exists(iKey) = False Then
            Res(k, 1) = iKey
            Dic.Add iKey, ""
          End If
        End If
        ik = Dic.Item(iKey2)
        tmp = sArr(i, 5)
        iTime = TimeValue(Mid(tmp, 1, 5)) + DateValue(Mid(tmp, 13, 4) & Mid(tmp, 9, 4) & Mid(tmp, 7, 2))
        Res(ik, 4) = iTime
      ElseIf sArr(i, 3) Like "Busy" Then
        If Dic.Exists(iKey2) Then
          ik = Dic.Item(iKey2)
          fTime = Res(ik, 4)
          If Len(fTime) Then
            tmp = sArr(i, 5)
            iTime = TimeValue(Mid(tmp, 1, 5)) + DateValue(Mid(tmp, 13, 4) & Mid(tmp, 9, 4) & Mid(tmp, 7, 2))
            Res(ik, 3) = Res(ik, 3) + iTime - fTime
            Res(ik, 4) = Empty
          End If
        End If
      End If
    Next i
  Set Dic = Nothing
  With Sheet1
    eRow = .Range("I" & Rows.Count).End(xlUp).Row
    If eRow > 1 Then .Range("H2:J" & eRow).Clear
    If k Then
      .Range("I2:I2").Resize(k).NumberFormat = "dd/mm/yyyy"
      .Range("J2:J2").Resize(k).NumberFormat = "h:mm"
      .Range("H2:J2").Resize(k) = Res
    End If
  End With
End Sub
Nhấp chuột vào đây để mở rộng...

Em cám ơn anh Hieu đã hỗ trợ, nhưng hình như kết quả chưa đúng, anh xem lại giúp em với:
Theo dữ liệu Nguyễn Văn B không có dữ liệu busy từ ngày 07/10/2019, Nguyễn Văn A ngày 01/10: busy tới 15:17 (không đúng dữ liệu em đếm tay)

2.PNG
 
shaming10 đã viết:
Em cám ơn anh Hieu đã hỗ trợ, nhưng hình như kết quả chưa đúng, anh xem lại giúp em với:
Theo dữ liệu Nguyễn Văn B không có dữ liệu busy từ ngày 07/10/2019, Nguyễn Văn A ngày 01/10: busy tới 15:17 (không đúng dữ liệu em đếm tay)

View attachment 227069
Nhấp chuột vào đây để mở rộng...
Chỉnh lại code
Mã: 
Sub Available_Busy()
  Dim sArr(), Res(), Arr, Dic As Object, iKey, iKey2$, tmp$
  Dim eRow&, sRow&, i&, r&, k&, ik&, iTime, fTime
  With Sheet1
    eRow = .Range("A" & Rows.Count).End(xlUp).Row
    If eRow < 2 Then MsgBox ("Khong co du lieu"): Exit Sub
    sArr = .Range("A2:E" & eRow).Value
  End With

  Set Dic = CreateObject("scripting.dictionary")
  sRow = UBound(sArr)
  ReDim Res(1 To sRow, 1 To 4)
  For i = 1 To sRow
    iKey = sArr(i, 1)
    If Dic.Exists(iKey) = False Then
      Dic.Add iKey, Array(i)
    Else
      Arr = Dic.Item(iKey)
      ReDim Preserve Arr(0 To UBound(Arr) + 1)
      Arr(UBound(Arr)) = i
      Dic.Item(iKey) = Arr
    End If
  Next i
  For Each iKey In Dic.keys
    Arr = Dic.Item(iKey)
    sRow = UBound(Arr)
    For r = 0 To sRow
      i = Arr(r)
      iKey2 = iKey & sArr(i, 2)
      If sArr(i, 3) Like "Available_Chat" Then
        If Not Dic.Exists(iKey2) Then
          k = k + 1
          Dic.Add iKey2, k
          tmp = sArr(i, 2)
          Res(k, 2) = DateValue(Mid(tmp, 7, 4) & Mid(tmp, 3, 4) & Mid(tmp, 1, 2))
          If Dic.Exists(iKey) Then
            Res(k, 1) = iKey
            Dic.Remove (iKey)
          End If
        End If
        ik = Dic.Item(iKey2)
        tmp = sArr(i, 5)
        iTime = TimeValue(Mid(tmp, 1, 5)) + DateValue(Mid(tmp, 13, 4) & Mid(tmp, 9, 4) & Mid(tmp, 7, 2))
        Res(ik, 4) = iTime
      ElseIf sArr(i, 3) Like "Busy" Then
        If Dic.Exists(iKey2) Then
          ik = Dic.Item(iKey2)
          fTime = Res(ik, 4)
          If Len(fTime) Then
            tmp = sArr(i, 5)
            iTime = TimeValue(Mid(tmp, 1, 5)) + DateValue(Mid(tmp, 13, 4) & Mid(tmp, 9, 4) & Mid(tmp, 7, 2))
            Res(ik, 3) = Res(ik, 3) + iTime - fTime
            Res(ik, 4) = Empty
          End If
        End If
      End If
    Next r
  Next iKey
  Set Dic = Nothing
  With Sheet1
    eRow = .Range("I" & Rows.Count).End(xlUp).Row
    If eRow > 1 Then .Range("H2:J" & eRow).Clear
    If k Then
      .Range("I2:I2").Resize(k).NumberFormat = "dd/mm/yyyy"
      .Range("J2:J2").Resize(k).NumberFormat = "h:mm"
      .Range("H2:J2").Resize(k) = Res
    End If
  End With
End Sub
 
HieuCD đã viết:
Chỉnh lại code
Mã: 
Sub Available_Busy()
  Dim sArr(), Res(), Arr, Dic As Object, iKey, iKey2$, tmp$
  Dim eRow&, sRow&, i&, r&, k&, ik&, iTime, fTime
  With Sheet1
    eRow = .Range("A" & Rows.Count).End(xlUp).Row
    If eRow < 2 Then MsgBox ("Khong co du lieu"): Exit Sub
    sArr = .Range("A2:E" & eRow).Value
  End With

  Set Dic = CreateObject("scripting.dictionary")
  sRow = UBound(sArr)
  ReDim Res(1 To sRow, 1 To 4)
  For i = 1 To sRow
    iKey = sArr(i, 1)
    If Dic.Exists(iKey) = False Then
      Dic.Add iKey, Array(i)
    Else
      Arr = Dic.Item(iKey)
      ReDim Preserve Arr(0 To UBound(Arr) + 1)
      Arr(UBound(Arr)) = i
      Dic.Item(iKey) = Arr
    End If
  Next i
  For Each iKey In Dic.keys
    Arr = Dic.Item(iKey)
    sRow = UBound(Arr)
    For r = 0 To sRow
      i = Arr(r)
      iKey2 = iKey & sArr(i, 2)
      If sArr(i, 3) Like "Available_Chat" Then
        If Not Dic.Exists(iKey2) Then
          k = k + 1
          Dic.Add iKey2, k
          tmp = sArr(i, 2)
          Res(k, 2) = DateValue(Mid(tmp, 7, 4) & Mid(tmp, 3, 4) & Mid(tmp, 1, 2))
          If Dic.Exists(iKey) Then
            Res(k, 1) = iKey
            Dic.Remove (iKey)
          End If
        End If
        ik = Dic.Item(iKey2)
        tmp = sArr(i, 5)
        iTime = TimeValue(Mid(tmp, 1, 5)) + DateValue(Mid(tmp, 13, 4) & Mid(tmp, 9, 4) & Mid(tmp, 7, 2))
        Res(ik, 4) = iTime
      ElseIf sArr(i, 3) Like "Busy" Then
        If Dic.Exists(iKey2) Then
          ik = Dic.Item(iKey2)
          fTime = Res(ik, 4)
          If Len(fTime) Then
            tmp = sArr(i, 5)
            iTime = TimeValue(Mid(tmp, 1, 5)) + DateValue(Mid(tmp, 13, 4) & Mid(tmp, 9, 4) & Mid(tmp, 7, 2))
            Res(ik, 3) = Res(ik, 3) + iTime - fTime
            Res(ik, 4) = Empty
          End If
        End If
      End If
    Next r
  Next iKey
  Set Dic = Nothing
  With Sheet1
    eRow = .Range("I" & Rows.Count).End(xlUp).Row
    If eRow > 1 Then .Range("H2:J" & eRow).Clear
    If k Then
      .Range("I2:I2").Resize(k).NumberFormat = "dd/mm/yyyy"
      .Range("J2:J2").Resize(k).NumberFormat = "h:mm"
      .Range("H2:J2").Resize(k) = Res
    End If
  End With
End Sub
Nhấp chuột vào đây để mở rộng...
Em cám ơn anh Hieu, file okie rồi nha anh :D
 
shaming10 đã viết:
Nếu mở rộng ra lấy thêm thời gian breaktime được không anh?
Nhấp chuột vào đây để mở rộng...
Đã có Anh @HieuCD ra tay là xong ngay. :)

Trả lời câu hỏi của bạn để bạn tham khảo thêm về công thức:
Chỉ cần sửa 1 chút công thức tại cột K (cột CP1- cột phụ 1), tức bất luận cứ 1 cặp "Available", và "Busy" hoặc "Break_Time" thì thỏa điều kiện để tính toán:
Mã: 
K2=IF(LEFT(C2)="B",IF(AND(LEFT(C1)="A",B2=B1,A2=A1),1,),IF(AND(LEFT(C2)="A",LEFT(C3)="B",B2=B3,A2=A3),1,))
Enter, fill xuống.

Thân
 

File đính kèm

  • Thoi gian busy.xlsx
    75.6 KB · Đọc: 10
Phan Thế Hiệp đã viết:
Đã có Anh @HieuCD ra tay là xong ngay. :)

Trả lời câu hỏi của bạn để bạn tham khảo thêm về công thức:
Chỉ cần sửa 1 chút công thức tại cột K (cột CP1- cột phụ 1), tức bất luận cứ 1 cặp "Available", và "Busy" hoặc "Break_Time" thì thỏa điều kiện để tính toán:
Mã: 
K2=IF(LEFT(C2)="B",IF(AND(LEFT(C1)="A",B2=B1,A2=A1),1,),IF(AND(LEFT(C2)="A",LEFT(C3)="B",B2=B3,A2=A3),1,))
Enter, fill xuống.

Thân
Nhấp chuột vào đây để mở rộng...
Em cám ơn anh Hiệp.
 
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

S
  • Question
Không lấy đủ dữ liệu bằng kết nối ADO
Trả lời
3
Đọc
110
Hoàng Trọng Nghĩa
Hoàng Trọng Nghĩa
S
Nhờ giúp đỡ code in toàn bộ danh sách có điều kiện
Trả lời
7
Đọc
158
HUONGHCKT
H
LuuAnh980
  • Question
Code lấy dữ liệu từ 3 file con.
2
Trả lời
34
Đọc
332
LuuAnh980
LuuAnh980
trantrimmo
Xin code VBA xuất pdf từ 3 sheet theo lấy thông tin theo tên sheet ạ.
Trả lời
1
Đọc
112
dangvandang
dangvandang
B
  • Question
Chuyển đổi dữ liệu sạng dạng mảng
Trả lời
6
Đọc
111
HieuCD
H
Xem thêm…

Facebook

Giaiphapexcel.com

Group

DIỄN ĐÀN GIẢI PHÁP EXCEL Group 1
DIỄN ĐÀN GIẢI PHÁP EXCEL Group 2

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Back
Top Bottom