Tính thời gian làm việc (1 người xem)

[xuanhonng]

Với giờ làm việc

[TABLE="width: 297"]
[TR]
[TD]Giờ làm việc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Sáng
[/TD]
[TD="align: right"]7:30
[/TD]
[TD="align: right"]11:30[/TD]
[/TR]
[TR]
[TD="align: right"]Chiều[/TD]
[TD="align: right"]13:30[/TD]
[TD="align: right"]17:30[/TD]
[/TR]
[/TABLE]
(trưa nghỉ 2 tiếng)


Có bảng 4 cột A , B, C , D E, tôi muốn tính cột E (dấu ???), bạn nào giúp với, cám ơn nhiều

[TABLE="width: 472"]
[TR]
[TD]TT
[/TD]
[TD]Họ tên[/TD]
[TD]Giờ đến[/TD]
[TD]Giờ về[/TD]
[TD="align: center"]Số giờ làm việc
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: left"]Bùi Chánh Tâm[/TD]
[TD="align: right"]7:31[/TD]
[TD="align: right"]16:32[/TD]
[TD="align: center"]???
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: left"]Dương Ngọc Huỳnh Như[/TD]
[TD="align: right"]7:57[/TD]
[TD="align: right"]15:57[/TD]
[TD="align: center"]???[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: left"]Bùi Thị Ánh Tuyết[/TD]
[TD="align: right"]13:20[/TD]
[TD="align: right"]17:40[/TD]
[TD="align: center"]???[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: left"]Nguyễn Thị Sang[/TD]
[TD="align: right"]7:30[/TD]
[TD="align: right"]8:44[/TD]
[TD="align: center"]???
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: left"]Diệp Minh Phát Tài[/TD]
[TD="align: right"]7:57[/TD]
[TD="align: right"]17:24[/TD]
[TD="align: center"]???[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: left"]Dương Ngọc Như [/TD]
[TD="align: right"]7:51[/TD]
[TD="align: right"]16:46[/TD]
[TD="align: center"]???[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: left"]Nguyễn Thị Dân[/TD]
[TD="align: right"]7:58[/TD]
[TD="align: right"]17:25[/TD]
[TD="align: center"]???[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: left"]Nguyễn Thanh Tùng[/TD]
[TD="align: right"]14:47[/TD]
[TD="align: right"]17:34[/TD]
[TD="align: center"]???[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: left"]Bùi Hiền Thủy[/TD]
[TD="align: right"]7:52[/TD]
[TD="align: right"]14:52[/TD]
[TD="align: center"]???[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="align: left"]Bùi Thế Trí[/TD]
[TD="align: right"]7:57[/TD]
[TD="align: right"]15:58[/TD]
[TD="align: center"]???[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: left"]Diệp Minh Tính[/TD]
[TD="align: right"]7:41[/TD]
[TD="align: right"]17:20[/TD]
[TD="align: center"]???
[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD="align: left"]Bùi Thế Thao[/TD]
[TD="align: right"]7:24[/TD]
[TD="align: right"]14:36[/TD]
[TD="align: center"]???[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD="align: left"]Nguyễn Thị Hồng[/TD]
[TD="align: right"]7:59[/TD]
[TD="align: right"]16:43[/TD]
[TD="align: center"]???[/TD]
[/TR]
[/TABLE]

 

[xuanhonng]

Vừa sửa lại dữ liệu, vì tôi đưa nhầm, có bạn nào giúp cho tôi với nhỉ, tôi muốn có công thức hay hàm gì đó tính cột E. cám ơn nhiều

 

[sealand]

Công thức cột E (Nhập công thức sau vào ô E2 rồi kéo xuống):

=D2-C2-IF(AND(C2<0.5,D2>0.5),TIME(2,0,0),0)

 

[VetMini]

Vấn đề này, nếu chỉ muốn dùng công thức thì bắt buộc công thức phải hơi khó hiểu một chút (ví dụ như sealand trong bài #3).

Nếu là bảng tính lương thì người ta làm theo kiểu dễ giải thích (cho công nhân) một chút, bằng cách thêm các 4 trị giờ bắt đầu sáng (BDS), kết thúc sáng (KTS), bắt đầu chiều (BDC), kết thúc chiều (KTC).

Nếu gọi giờ đến là GD và giờ về là GV thì công thức là:
= Max(Min(KTS,GV) - Max(GD,BDS), 0) + Max(Min(KTC,GV) - Max(GD,BDC), 0)

*lưu ý: phương pháp trên tính giờ theo đúng ý như giờ quy định, nếu công nhân đến sớm hơn 7:30 hoặc về trễ hơn 17:30 thì họ tự chịu thiệt.
Công thức của bài #3 tính đủ hết.

Lần sau đưa bài lên như thế thì ví dụ luôn kết quả, để người ta còn suy đoán cách sử lý trường hợp bất thường (đến sớm về trễ). Làm ăn vắn tắt như bạn nhỡ công nhân người ta kiện lên công đoàn thì lấy gì giải thích?

 

[sealand]

Đúng như bác Vet Mini đã nói, nếu xác định công thế này thì chưa thể đáp ứng được. Còn phải xác đinh đi muộn về sớm, tăng ca tăng giờ v.v... nữa. Ở đây, trước mắt chỉ gợi ý bạn cách xác định thời gian mà thôi.
Công thức có thể hơi khó hiểu nên mình xin chẻ nó ra 1 chút.

1/Toàn thời gian sẽ bằng giờ ra trừ giờ vào (D2-C2)
2/Trừ thời gian nghỉ trưa:
Chỉ trừ đối với người vào trước 12 giờ trưa (TimeSerial=0,5) và ra sau 12 giờ trưa. Ai vào và ra trong buổi sáng hay chiều thì không trừ.

IF(AND(C2<0.5,D2>0.5),TIME(2,0,0),0)

 

[xuanhonng]

Cám ơn các bạn đã giúp, công thức của sealand thì chưa đúng, ví dụ trường hợp
[TABLE="width: 472"]
[TR]
[TD]TT[/TD]
[TD="align: left"]Họ tên[/TD]
[TD="align: center"]Giờ đến[/TD]
[TD="align: center"]Giờ về[/TD]
[TD="align: center"]Số giờ làm việc[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: left"]Bùi Thị Ánh Tuyết[/TD]
[TD="align: center"]13:20[/TD]
[TD="align: center"]17:40[/TD]
[TD="align: center"]4:20[/TD]
[/TR]
[/TABLE]

nhẩm ra cũng thấy chỉ là 4:00, chỉ tính trong giờ làm quy định

Đang thử công thức của VietMini nhưng khó hiểu quá, chưa áp dụng được.

 

[tranhoangquoc]

Bạn định dạng cho mấy cột "Giờ đến", "Giờ về" và "Số giờ làm việc" là "Time" trong Format Cells--> tap Number
rồi cho "Số giờ làm việc = Giờ về - Giờ đến" là xong

 

[xuanhonng]

Bạn định dạng cho mấy cột "Giờ đến", "Giờ về" và "Số giờ làm việc" là "Time" trong Format Cells--> tap Number
rồi cho "Số giờ làm việc = Giờ về - Giờ đến" là xong

Kết quả mong muốn (cột E, chữ đỏ) như thế này bạn ah,

[TABLE="width: 472"]
[TR]
[TD]TT
[/TD]
[TD]Họ tên[/TD]
[TD="align: center"]Giờ đến
[/TD]
[TD="align: center"]Giờ về[/TD]
[TD="align: center"]Số giờ làm việc[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bùi Chánh Tâm[/TD]
[TD="align: center"]7:31[/TD]
[TD="align: center"]16:32[/TD]
[TD="align: center"]7:01
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Dương Ngọc Huỳnh Như[/TD]
[TD="align: center"]7:57[/TD]
[TD="align: center"]15:57[/TD]
[TD="align: center"]6:00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Bùi Thị Ánh Tuyết[/TD]
[TD="align: center"]13:20[/TD]
[TD="align: center"]17:40[/TD]
[TD="align: center"]4:00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Nguyễn Thị Sang[/TD]
[TD="align: center"]7:30[/TD]
[TD="align: center"]8:44[/TD]
[TD="align: center"]1:14[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Diệp Minh Phát Tài[/TD]
[TD="align: center"]7:57[/TD]
[TD="align: center"]17:24[/TD]
[TD="align: center"]7:27[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Dương Ngọc Như[/TD]
[TD="align: center"]7:51[/TD]
[TD="align: center"]16:46[/TD]
[TD="align: center"]6:55[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Nguyễn Thị Dân[/TD]
[TD="align: center"]7:58[/TD]
[TD="align: center"]17:25[/TD]
[TD="align: center"]7:27[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Nguyễn Thanh Tùng[/TD]
[TD="align: center"]14:47[/TD]
[TD="align: center"]17:34[/TD]
[TD="align: center"]2:43
[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Bùi Hiền Thủy[/TD]
[TD="align: center"]7:52[/TD]
[TD="align: center"]14:52[/TD]
[TD="align: center"]5:00[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Bùi Thế Trí[/TD]
[TD="align: center"]7:57[/TD]
[TD="align: center"]15:58[/TD]
[TD="align: center"]6:01[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Diệp Minh Tính[/TD]
[TD="align: center"]7:41[/TD]
[TD="align: center"]17:20[/TD]
[TD="align: center"]7:39[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Bùi Thế Thao[/TD]
[TD="align: center"]7:24[/TD]
[TD="align: center"]14:36[/TD]
[TD="align: center"]5:06[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Nguyễn Thị Hồng[/TD]
[TD="align: center"]7:59[/TD]
[TD="align: center"]16:43[/TD]
[TD="align: center"]6:44[/TD]
[/TR]
[/TABLE]

Chưa xem file nhưng cách làm bạn nói không ra kết quả

 

[tranhoangquoc]

Mình vẫn chưa hiểu bạn muốn tính cột "Số giờ làm việc" như thế nào!!!
bạn có thể nói rõ chút được không?

 

[tranhoangquoc]

Là nếu làm từ sáng tới chều là trừ 2 giờ nghĩ trưa hả bạn

 

[xuanhonng]

Mình vẫn chưa hiểu bạn muốn tính cột "Số giờ làm việc" như thế nào!!!
bạn có thể nói rõ chút được không?

Cám ơn bạn,
ở đây, vấn đề chỉ tính giờ làm việc thực sự trong giờ hành chính của các CNV, giờ làm việc hành chính đã nói trên: sáng 7h30--11h30, chiều 13h30--17h30

 

[sealand]

Đoán 1 lần nữa:

-Đi sớm trước 7:30 chỉ tính từ 7:30
-Về trong gio nghỉ trưa chỉ tính đến 11:30
-Đến trong giò nghỉ trưa chỉ tính từ 13:30
-Nghỉ sau 17:30 chỉ tính đến 17:30

Xác định như thế này thì đúng với số của bạn

Tham khảo file ví dụ, mình dùng mấy cái Name cho gọn công thức

 

[Hau151978]

Cám ơn bạn,
ở đây, vấn đề chỉ tính giờ làm việc thực sự trong giờ hành chính của các CNV, giờ làm việc hành chính đã nói trên: sáng 7h30--11h30, chiều 13h30--17h30

Bạn thử công thức này

=IF(OR(C1>TIME(13,30,0),D1<TIME(11,30,0)),D1-C1,MAX(TIME(11,30,0)-C1,0)+MAX(D1-TIME(13,30,0),0))

Giải thích:
- Nếu giờ đến sau 13h30 hoặc giờ về trước 11h30 thì kết quả là giờ về - giờ đến (C1-D1).
- Nếu không phải trường hợp trên (tức là C1<=13h30 và D1>=11h30) thì
+ Thời gian làm việc sáng là MAX(TIME(11,30,0)-C1,0)
+ Thời gian làm chiều là MAX(D1-TIME(13,30,0),0)
+ Thời gian làm cả ngày là tổng 2 số.

 

[tranhoangquoc]

Mình làm file này, công thức thì phần lớn đúng, bạn xem thử dc chưa, vì chưa hiểu hết ý của bạn, xem rồi mình sửa thêm!!!

 

[ec ec]

Cám ơn bạn,
ở đây, vấn đề chỉ tính giờ làm việc thực sự trong giờ hành chính của các CNV, giờ làm việc hành chính đã nói trên: sáng 7h30--11h30, chiều 13h30--17h30

công thức cho dòng đầu tiên

=IF(A2>B2,"",IF(SUM((B2>=TIME({7,13},{30,30},{0,0}))*(B2<=TIME({11,17},{30,30},{0,0}))),B2,IF(SUM((B2>=TIME({7,13},{30,30},{0,0}))*(B2<=TIME({11,17},{30,30},{0,0}))),B2,LOOKUP(1,1/(B2>=TIME({11,13,17},{30,30,30},{0,0,0})),TIME({11,13,17},{30,30,30},{0,0,0}))))-IF(SUM((A2>=TIME({7,13},{30,30},{0,0}))*(A2<=TIME({11,17},{30,30},{0,0}))),A2,INDEX(TIME({7,11,13},{30,30,30},{0,0,0}),MATCH(1,1/(A2<=TIME({7,11,13},{30,30,30},{0,0,0})),0)))-1/12*(A2<=TIME(11,30,0)*(B2>=TIME(13,30,0))))

 

[xuanhonng]

Cám ơn tất cả các bạn
Qua thử công thức thì công thức của Sealand đã đúng, và công thức của VietMini đã đúng kết quả như mong muốn.
Còn lại các công thức khác thì chưa chính xác, riêng công thức ecec thì dài quá thử chỉ ra #N/A.