[Xin giúp đỡ] Công thức tính thời gian làm được tính tiền mà không tính thời gian nghỉ (3 người xem)

[Lá chanh]

Mình có 1 bảng thời gian máy làm việc không được tính tiền và dữ liệu thời gian máy chạy. Cần tính "thời gian làm việc được tính tiền" của máy dựa trên dữ liệu làm việc của máy. Nhờ các bác giúp công thức để tính toán.
Ví dụ:
-Thời gian máy làm việc nhưng không được tính tiền là từ 10:00-11:00 ....( còn một số khoảng thời gian không đc tính tiền nữa nhưng mình không viết vào đây)
-Ngày 01/06/2023 Máy làm việc từ 9:00 đến 12:10. thì "thời gian làm việc được tính tiền"=9:00-10:00+ 11:00-12:10 = 60'+70'=130'
Chi tiết các bác xem file đính kèm và giúp em nhé,
Cảm ơn cả nhà!!!

 

[bebo021999]

1 Phương án dùng UDF (User Define Function) là hàm tự tạo nhé
Cú pháp:
=TinhTien(Vùng TG không tính tiền, TG bắt đầu, TG kết thúc)
VD: tại E10:
=tinhtien($C$3:$D$6,C10,D10)

Cách dùng:
Alt-F11 mở cửa sổ VBA, insert / module, copy code này vô:
Save as file dạng .xlsm

Option Explicit
Function TinhTien(ByVal tg As Range, bd As Range, kt As Range)
Dim i&, j&, h1 As Double, h2 As Double, sum&
Dim dic As Object, key
Set dic = CreateObject("Scripting.Dictionary")
TinhTien = tg.Columns(1).Cells(1, 1)
For i = 1 To 4
    h1 = tg.Columns(1).Cells(i, 1): h2 = tg.Columns(1).Cells(i, 2)
    h1 = Hour(h1) * 60 + Minute(h1): h2 = Hour(h2) * 60 + Minute(h2)
    For j = h1 + 1 To h2
        dic.Add j, ""
    Next
Next
h1 = Hour(bd) * 60 + Minute(bd): h2 = Hour(kt) * 60 + Minute(kt)
For i = h1 + 1 To h2
    If Not dic.exists(i) Then sum = sum + 1
Next
TinhTien = sum
Set dic = Nothing
End Function

 

[Lá chanh]

Cảm ơn bạn @bebo021999 . mình sẽ thử cách của bạn. Tuy nhiên, có thể sử dụng hàm excel không nhỉ? Do file của mình để trên server công ty lại không thể chạy được file .xlsm

 

[Lá chanh]

Up. Mong các cao thủ đi qua giúp e 1 chút. Đang cần quá

 

[Lá chanh]

up. nhờ giúp đỡ ạ

 

[cantl]

Muốn hàm thì xem trong này, nếu hiểu thì tự sửa được.
Sở dĩ tớ không viết hàm cho bạn vì tớ vẫn chưa hiểu được cách hoạt động của hàm mặc dù đọc đi đọc lại mấy lần.
Nói chung là khó quá.

up. nhờ giúp đỡ ạ

Hàm tính thời gian thao tác

Chào các bạn, - Mình đang xây dựng công thức tính thời gian thao tác của nhân viên, trong quá trình làm việc xảy ra các yêu cầu như sau: Nghỉ giải lao 10ph từ 10h đến 10h10 Nghỉ trưa 60ph từ 12h đến 13h Nghỉ giải lao 10ph từ 15h đến 15h10 - Vì thời gian thao tác của nhân viên khi nhập vào bị...

www.giaiphapexcel.com

 

[htin1997]

Không tính giây nha, chỉ tính phút. Không rành lắm nên hơi rườm rà. Hy vọng hiểu ý và giải quyết đúng vấn đề.

 

[Nhattanktnn]

Cảm ơn bạn @bebo021999 . mình sẽ thử cách của bạn. Tuy nhiên, có thể sử dụng hàm excel không nhỉ? Do file của mình để trên server công ty lại không thể chạy được file .xlsm

Thử công thức dưới, ctrl+shift+enter:

=(D10-C10-SUM((C10<=$C$3:$C$6)*(D10>=$D$3:$D$6)*($D$3:$D$6-$C$3:$C$6))-SUM((C10:D10<$D$3:$D$6)*(C10:D10>$C$3:$C$6)*IF({1,0},$D$3:$D$6-C10,D10-$C$3:$C$6)))*1440

 

[HieuCD]

Mình có 1 bảng thời gian máy làm việc không được tính tiền và dữ liệu thời gian máy chạy. Cần tính "thời gian làm việc được tính tiền" của máy dựa trên dữ liệu làm việc của máy. Nhờ các bác giúp công thức để tính toán.
Ví dụ:
-Thời gian máy làm việc nhưng không được tính tiền là từ 10:00-11:00 ....( còn một số khoảng thời gian không đc tính tiền nữa nhưng mình không viết vào đây)
-Ngày 01/06/2023 Máy làm việc từ 9:00 đến 12:10. thì "thời gian làm việc được tính tiền"=9:00-10:00+ 11:00-12:10 = 60'+70'=130'
Chi tiết các bác xem file đính kèm và giúp em nhé,
Cảm ơn cả nhà!!!

Hàm round làm tròn phút

=ROUND((D10-C10-SUM(--TEXT(IF($D$3:$D$6<D10,$D$3:$D$6,D10)-IF($C$3:$C$6>C10,$C$3:$C$6,C10),"[<0]\0")))*1440,0)

Nhấn ctrl+shift+enter

 

[Lá chanh]

Cảm ơn @Nhattanktnn @HieuCD . Thật tuyệt vời, đúng thứ mình cần rồi.

 

[Lá chanh]

Hàm round làm tròn phút

=ROUND((D10-C10-SUM(--TEXT(IF($D$3:$D$6<D10,$D$3:$D$6,D10)-IF($C$3:$C$6>C10,$C$3:$C$6,C10),"[<0]\0")))*1440,0)

Nhấn ctrl+shift+enter

Chào bạn! công thức này của bạn mình áp dụng vào trường hợp này thì thấy nó cho kết quả không đúng. (vì không hiểu bản chất của công thức nên không hiểu vì sao không cho kết quả đúng)
Bạn xem hộ mình công thức mình viết có đúng không?
Mình cảm ơn

 

[Phan Thế Hiệp]

Chào bạn! công thức này của bạn mình áp dụng vào trường hợp này thì thấy nó cho kết quả không đúng. (vì không hiểu bản chất của công thức nên không hiểu vì sao không cho kết quả đúng)
Bạn xem hộ mình công thức mình viết có đúng không?
Mình cảm ơnView attachment 299299

Thử E5:

=D5-C5-SUM(1-(C5>=$G$2:$G$4)-(D5<=$F$2:$F$4))

Kết thúc bằng Ctrl+Shift+Enter.

Thân

 

[HieuCD]

Chào bạn! công thức này của bạn mình áp dụng vào trường hợp này thì thấy nó cho kết quả không đúng. (vì không hiểu bản chất của công thức nên không hiểu vì sao không cho kết quả đúng)
Bạn xem hộ mình công thức mình viết có đúng không?
Mình cảm ơnView attachment 299299

Bạn nhập dư hàm SUM

Bài đã được tự động gộp: 1/3/24

Thử E5:

=D5-C5-SUM(1-(C5>=$G$2:$G$4)-(D5<=$F$2:$F$4))

Kết thúc bằng Ctrl+Shift+Enter.

Thân

Công thức hại não như vậy mà bạn cũng nghĩ ra, quá tuyệt vời.

 

[Phan Thế Hiệp]

Công thức hại não như vậy mà bạn cũng nghĩ ra, quá tuyệt vời.

Ăn may thôi anh!

Hì hì hì
Chúc anh ngày thiệt vui.

 

[Lá chanh]

@HieuCD @Phan Thế Hiệp 2 bác có sách gì hay nguồn nào để học nâng cao excel, Giúp em có thể đọc hiểu và xa hơn nữa là có thể viết những công thức như này không ạ? Giới thiệu em với!

 

[HieuCD]

@HieuCD @Phan Thế Hiệp 2 bác có sách gì hay nguồn nào để học nâng cao excel, Giúp em có thể đọc hiểu và xa hơn nữa là có thể viết những công thức như này không ạ? Giới thiệu em với!

Mình chỉ có cuốn sách GPE

 

[Phan Thế Hiệp]

@HieuCD @Phan Thế Hiệp 2 bác có sách gì hay nguồn nào để học nâng cao excel, Giúp em có thể đọc hiểu và xa hơn nữa là có thể viết những công thức như này không ạ? Giới thiệu em với!

Anh @HieuCD có cuốn sách bí quyết dạy học các môn không chỉ có mỗi môn Excel, từ vỡ lòng đến nâng cao, đó là cuốn: "Khiêm động học luyện tích năng".

Bạn hỏi xin ảnh thử, chỉ sợ ảnh giấu làm của mà thôi!.

Thân

 

[Lá chanh]

Xin phép các bác em đào lại bài này. Trước đây em nhờ các bác công thức 1 file tương tự ( ở bài #1). Nhưng khi đó công ty chỉ làm ca 1 và ca 2 nên mọi chuyện êm đẹp. Nay công ty phát sinh thêm đi ca Hành chính và Ca3 nên công thức lần trước không dùng được nữa. Nay Em xin nhờ các bác giúp đỡ Em tính thời gian thực tế làm việc sau khi trừ đi thời gian nghỉ a.
( Em cũng đã mày mò tìm hiểu công thức của các bác. Nhưng mà đầu óc Em hơi tối nên không thể áp dùng gì được trong trường hợp này)
Ngồi cắn bút chờ tin

 

[Lá chanh]

Các bác ơi! Giúp Em với!

 

[huuthang_bd]

Xin phép các bác em đào lại bài này. Trước đây em nhờ các bác công thức 1 file tương tự ( ở bài #1). Nhưng khi đó công ty chỉ làm ca 1 và ca 2 nên mọi chuyện êm đẹp. Nay công ty phát sinh thêm đi ca Hành chính và Ca3 nên công thức lần trước không dùng được nữa. Nay Em xin nhờ các bác giúp đỡ Em tính thời gian thực tế làm việc sau khi trừ đi thời gian nghỉ a.
( Em cũng đã mày mò tìm hiểu công thức của các bác. Nhưng mà đầu óc Em hơi tối nên không thể áp dùng gì được trong trường hợp này)
Ngồi cắn bút chờ tin

=(H10-G10+(H10<G10)-SUM(IFERROR((MMULT(OFFSET($K$4:$L$6,,MATCH(E10,$K$2:$R$2,0)-1),{1,1,0,0;0,0,1,1})-SMALL(G10:H10+(E10="Ca3")*(G10:H10<1/2),{1,2,1,2}))^0.5^2,0)*{-1,1,1,-1}))*1440

Hoặc

=(H10-G10+(H10<G10)-SUM(--TEXT(MMULT((G10:H10+(E10="Ca3")*(G10:H10<1/2))*{-1,1}-TEXT((G10:H10+(E10="Ca3")*(G10:H10<1/2)-OFFSET($K$4:$L$6,,MATCH(E10,$K$2:$R$2,0)-1))*{-1,1},"[<0]\0"),{1;1}),"[<0]\0")))*1440

Giá trị bảng dò Ca3 được sửa lại (cộng 1 vào các giá trị nhỏ hơn 6:00) để công thức đơn giản hơn.