[Mong anh em GPE giúp đỡ] Cộng thời gian hơn 24 tiếng với hàm TIME ngày không tự động cộng dồn lên (1 người xem)

[Nhoc247]

Chào các anh/chị trong diễn đàn,
Hai bữa nay mình ngồi search google, mò công thức mà không tìm được cách để giải quyết vấn đề này. Mong mọi người giúp đỡ.

* Vấn đề: Mình đang làm cho một công ty dịch vụ, sếp bảo mình làm một file excel tự động tính ngày giờ đưa sản phẩm vào phòng lab thí nghiệm. Hầu như mình đã giải quyết được hết các yêu cầu nhưng chỉ mắc kẹt một chỗ là mình dùng hàm TIME để cộng thời gian hơn 24 tiếng thì ngày không tự động nhảy theo được. Nhờ các anh/chị có thể chỉ giúp mình dùng hàm gì để giải quyết không?
Để hiểu rõ hơn mình có liệt kê cụ thể trường hợp của mình bên dưới và file đang thực hiện. Hy vọng sẽ có cao nhân quan tâm và giúp đỡ.

*Thông tin cụ thể
- 4 mục chính cần có:
+ Service type : Regular, Express, Double Express, Emergency.
+ Giờ sản phẩm vào phòng thí nghiệm (Lab in)
+ Giờ sản phẩm vào phòng thí nghiệm (Lab out)
+ Giờ hoàn thành báo cáo kết quả (Report out)

- Thông tin:
Thời gian làm việc: từ Thứ 2 --> T6
-Đối với dịch vụ Regular:
Lab out = Lab in + 68 tiếng
Lab out sau 8 Pm và trước 7 AM thì để Lab out là 8 AM ngày hôm sau.
Report out = Lab out + 9 tiếng
Report out sau 8 PM và Trước 7PM thì để Report out là 7 AM.
-Đối với dịch vụ Express:
Lab out = Lab in + 48 tiếng
Lab out sau 8 Pm và trước 7 AM thì để Lab out là 8 AM ngày hôm sau.
Report out = Lab out + 3 tiếng
Report out sau 8 PM và Trước 7PM thì để Report out là 7 AM.
-Đối với dịch vụ Double Express:
Lab out = Lab in + 27 tiếng
Lab out sau 8 Pm và trước 7 AM thì để Lab out là 8 AM ngày hôm sau.
Report out = Lab out + 3 tiếng
Report out sau 8 PM và Trước 7PM thì để Report out là 7 AM.
- Đối với dịch vụ Emergency thì không cần công thức.

 

[befaint]

.

 

[Nhoc247]

.

Không biết Befaint bao nhiêu tuổi nên mình xưng là bạn nhé. Cảm ơn bạn Befaint rất nhiều nha!
Xem file của bạn làm giúp mình thì nhận ra do mình biết quá ít hàm và áp dụng nó vào thực tế. Excel đó giờ toàn tự mò không.... Không biết bạn có thể giới thiệu khóa học hoặc nơi nào dạy sử dụng excel tốt nhất không, vì có lẽ từ giờ mình sẽ phải làm việc nhiều với excel.
Một lần nữa cảm ơn bạn đã giúp đỡ nhé!

 

[befaint]

có thể giới thiệu khóa học hoặc nơi nào dạy sử dụng excel

Bạn tải lại file ở bài trên nhé. Chỉnh lại chút trong công thức tính giờ Report out.

Nếu tới trung tâm học thì bạn cứ bảo có người giới thiệu nhóe!

http://www.bluesofts.net/daotaothuchanh/trung-tam-dao-tao-thuc-hanh-bluesofts.html

 

[VetMini]

Không biết Befaint bao nhiêu tuổi nên mình xưng là bạn nhé. Cảm ơn bạn Befaint rất nhiều nha!

Bạn này không biết bao nhiêu tuổi nhưng tương đối cao.

 

[Nhoc247]

.

Befaint ơi, xem lại giúp mình file này được không. Hôm nay mình test thử với ngày 6 tháng 7 năm 2017 với dịch vụ Express và Double Express thì bị sai vì ngày lab out rơi vào thứ 7, công thức bên bản phụ không nhận diện được còn có thêm ngày CN không làm việc nên ra kết quả sai mất.
Mình cố gắng đọc công thức nhưng không hiểu hết ý nghĩa của nó nên càng sửa càng sai. Có thể giúp mình coi lại một lần nữa không?
Rất cảm ơn nếu bạn giúp thêm lần nữa.

 

[befaint]

Hôm nay mình test thử với ngày 6 tháng 7 năm 2017 với dịch vụ Express và Double Express thì bị sai vì ngày lab out rơi vào thứ 7, công thức bên bản phụ không nhận diện được còn có thêm ngày CN không làm việc nên ra kết quả sai mất.

Thi thoảng người ta để lại cái gì đó (...) nếu chủ thớt quan tâm thì sẽ sửa lại. Và mình đã sửa lại từ thứ Bảy tuần trước rồi nhé.
"Bạn tải lại file ở bài trên nhé. Chỉnh lại chút trong công thức tính giờ Report out."

 

[Nhoc247]

Thi thoảng người ta để lại cái gì đó (...) nếu chủ thớt quan tâm thì sẽ sửa lại. Và mình đã sửa lại từ thứ Bảy tuần trước rồi nhé.
"Bạn tải lại file ở bài trên nhé. Chỉnh lại chút trong công thức tính giờ Report out."

Qua giờ bận quá chưa phản hôi lại cho Befaint được.
Em đã coi lại rất kỹ 2 file, và đã download bản mới nhất khi thấy bình luận mới của anh (nghe đồn khá lớn tuổi nên em xin phép gọi Befaint là anh nhé). Và khi đưa vào thực tế làm thì công thức không còn đúng trong trường hợp sau:
+ Lab in: ngày 06 tháng 07 năm 2017, 09 giờ 00 phút
+ Dịch vụ: Express hoặc Double Express
--> Em có ngồi nghiên cứu thì thấy là do công thức tính số ngày thứ bảy và CN chỉ xét được 1 lần khi ở cột phụ, nên kết quả Lab out ra là ngày CN.
Đã thử tự sửa lại code nhưng càng sửa càng sai, do khả năng về sử dụng công thức còn hạn chế quá nên mới nhờ anh coi giúp lại.
P/s: Đính kèm là file em đã cố sửa nhưng vẫn bị vướng vấn đề em nêu trên.

 

[quocgiacan]

Qua giờ bận quá chưa phản hôi lại cho Befaint được.
Em đã coi lại rất kỹ 2 file, và đã download bản mới nhất khi thấy bình luận mới của anh (nghe đồn khá lớn tuổi nên em xin phép gọi Befaint là anh nhé). Và khi đưa vào thực tế làm thì công thức không còn đúng trong trường hợp sau:
+ Lab in: ngày 06 tháng 07 năm 2017, 09 giờ 00 phút
+ Dịch vụ: Express hoặc Double Express
--> Em có ngồi nghiên cứu thì thấy là do công thức tính số ngày thứ bảy và CN chỉ xét được 1 lần khi ở cột phụ, nên kết quả Lab out ra là ngày CN.
Đã thử tự sửa lại code nhưng càng sửa càng sai, do khả năng về sử dụng công thức còn hạn chế quá nên mới nhờ anh coi giúp lại.
P/s: Đính kèm là file em đã cố sửa nhưng vẫn bị vướng vấn đề em nêu trên.

Thử:

F3=WORKDAY(F2,H3/24,"0000011")+MOD(MOD(F2,1)+H3/24,1)
F4=F3+H4/24

Chúc bạn ngày vui.

 

[Nhoc247]

Thử:

F3=WORKDAY(F2,H3/24,"0000011")+MOD(MOD(F2,1)+H3/24,1)
F4=F3+H4/24

Chúc bạn ngày vui.

Cảm ơn anh Quocgiacan đã giúp nhé.
Khi đưa code này vào thì chỉ đúng trong trường hợp Express hoặc Double Express, còn Regular thì lại ra sai.... Chắc là mình phải làm theo kiểu hơi chuối tí, mỗi loại xài một kiểu công thức quá....

 

[quocgiacan]

Cảm ơn anh Quocgiacan đã giúp nhé.
Khi đưa code này vào thì chỉ đúng trong trường hợp Express hoặc Double Express, còn Regular thì lại ra sai.... Chắc là mình phải làm theo kiểu hơi chuối tí, mỗi loại xài một kiểu công thức quá....

Tôi quên, Workday() chỉ tính ngày không tính giờ nên cộng thiếu chút:

F3=WORKDAY(F2,MOD(F2,1)+H3/24,"0000011")+MOD(F2+H3/24,1)

Fill xuống cho F4 hay dùng công thức trên cho F4 cũng được.

Chúc bạn ngày vui.

 

[VetMini]

Anh bạn này cũng không biết bao nhiêu tuổi nhưng chắc cao.

 

[Nhoc247]

Tôi quên, Workday() chỉ tính ngày không tính giờ nên cộng thiếu chút:

F3=WORKDAY(F2,MOD(F2,1)+H3/24,"0000011")+MOD(F2+H3/24,1)

Fill xuống cho F4 hay dùng công thức trên cho F4 cũng được.

Chúc bạn ngày vui.

Nhờ vả nhiều quá cũng ngại ghê, nhưng không nhờ các anh giúp chắc em không xong nổi vụ này....
Công thức anh cho em dùng để xác định ngày Lab out dùng rất chính xác nên đã đưa vào bảng tính phụ.
Em vẫn còn bị vướng cái xét điều kiện kết quả ra sau 8 giờ tối thì hiển thị Lab out là 8 giờ sáng ngày hôm sau và trước 7 giờ sáng thì hiển thị là 8 giờ sáng. Định dùng hàm anh Befaint đã cho nhưng trình còi quá không sửa được.

=IF(INT(G3)*24>20,INT(G3)+1+8/24,IF(INT(G3)*24<7,INT(G3)+1+8/24,INT(G3)))
---> dù kết quả ra là 5 giờ chiều nó vẫn cộng thêm 1 ngày và hiển thị 8 giờ sáng ngày hôm sau...

Nhờ anh giúp đỡ thêm lần nữa nhé.
(File đính kèm đề phòng em viết lằng nhằng anh không hiểu được )
Cảm ơn anh!

 

[befaint]

Nhờ vả nhiều

Cái này thì thoải mái, chỉ sợ người nhờ bị đau. Chứ nhờ giúp nhiều mới ngại.

điều kiện kết quả ra sau 8 giờ tối thì hiển thị Lab out là 8 giờ sáng ngày hôm sau và trước 7 giờ sáng thì hiển thị là 8 giờ sáng

Cái điều kiện này không có ở bài #1.
"sau 8 Pm và trước 7 AM thì để Lab out là 8 AM ngày hôm sau".

-----------
Bạn tải file ở bài #2 nhé.

 

[Nhoc247]

Cái này thì thoải mái, chỉ sợ người nhờ bị đau. Chứ nhờ giúp nhiều mới ngại.

Em không hiểu rõ ý anh lắm. Thật ra em chỉ muốn nói là đã rất quan tâm đến những vấn đề anh đề cập và cập nhật file ngay khi anh nói.
Em đã cố gắng xem xét, nghiên cứu kỹ trước khi nói để không hỏi bừa hoặc không tự làm nếu có thể, tránh mất thời gian của anh. Có thể do vậy em phản hồi lại khá chậm khiến anh hiểu lầm

Cái điều kiện này không có ở bài #1.
"sau 8 Pm và trước 7 AM thì để Lab out là 8 AM ngày hôm sau"

Dạ, đúng rồi anh. Do lần trước em cung cấp thông tin cho anh bị sai sót, chỉ để là sau 8PM và trước 7AM thì để là 8 giờ ngày hôm sau. Hôm nay em mới để ý do em viết không rõ ràng chứ em không có ý nói hàm anh viết cho em bị sai...

 

[quocgiacan]

Nhờ vả nhiều quá cũng ngại ghê, nhưng không nhờ các anh giúp chắc em không xong nổi vụ này....
Công thức anh cho em dùng để xác định ngày Lab out dùng rất chính xác nên đã đưa vào bảng tính phụ.
Em vẫn còn bị vướng cái xét điều kiện kết quả ra sau 8 giờ tối thì hiển thị Lab out là 8 giờ sáng ngày hôm sau và trước 7 giờ sáng thì hiển thị là 8 giờ sáng. Định dùng hàm anh Befaint đã cho nhưng trình còi quá không sửa được.
=IF(INT(G3)*24>20,INT(G3)+1+8/24,IF(INT(G3)*24<7,INT(G3)+1+8/24,INT(G3)))
---> dù kết quả ra là 5 giờ chiều nó vẫn cộng thêm 1 ngày và hiển thị 8 giờ sáng ngày hôm sau...
Nhờ anh giúp đỡ thêm lần nữa nhé.
(File đính kèm đề phòng em viết lằng nhằng anh không hiểu được )
Cảm ơn anh!

Anh @befaint chỉ đùa chút với bạn cho vui tí.

Điều kiện của bạn hơi khá ngộ:

điều kiện kết quả ra sau 8 giờ tối thì hiển thị Lab out là 8 giờ sáng ngày hôm sau và trước 7 giờ sáng thì hiển thị là 8 giờ sáng

Vậy từ 7:00AM đến 8:00AM thì tính như thế nào?

Tôi nghĩ: Nếu kết quả ra khoảng thời gian: >20:00 ngày hôm nay, và <8:00 ngày hôm sau thì ghi nhận 8:00 ngày hôm sau.

Nếu đúng thì bạn thực hiện như sau:
G4: cột phụ chứa ngày Labout đã loại bỏ ngày thứ 7 và CN (công thức bài #11)
G5: số giờ thêm để báo cáo. (công thức bài #9)

F4=IF(G4<INT(G4)+1/3,INT(G4)+1/3,IF(G4>INT(G4)+5/6,INT(G4)+4/3,G4))

Fill xuống cho F5

Bạn tham khảo file kèm.

Chúc bạn ngày vui.

 

[befaint]

Bạn chủ thớt tải file ở bài #2 nhé.

 

[Nhoc247]

Bạn chủ thớt tải file ở bài #2 nhé.

Em đã tải và đã giải quyết được vấn đề nhờ code của anh. Cảm ơn anh rất nhiều.
Chúc anh một ngày tốt lành.

 

[Nhoc247]

Em đã giải quyết được bài toán khó này, đang kiểm tra lại nhiều trường hợp khác nhau xem có bị lỗi không. Cảm ơn anh đã giúp đỡ nhiệt tình.

Anh @befaint chỉ đùa chút với bạn cho vui tí.

Điều kiện của bạn hơi khá ngộ:

Vậy từ 7:00AM đến 8:00AM thì tính như thế nào?

Tôi nghĩ: Nếu kết quả ra khoảng thời gian: >20:00 ngày hôm nay, và <8:00 ngày hôm sau thì ghi nhận 8:00 ngày hôm sau.

Nếu đúng thì bạn thực hiện như sau:
G4: cột phụ chứa ngày Labout đã loại bỏ ngày thứ 7 và CN (công thức bài #11)
G5: số giờ thêm để báo cáo. (công thức bài #9)

F4=IF(G4<INT(G4)+1/3,INT(G4)+1/3,IF(G4>INT(G4)+5/6,INT(G4)+4/3,G4))

Fill xuống cho F5

Bạn tham khảo file kèm.

Chúc bạn ngày vui.

Chắc do em ghi ý không rõ ràng, chính xác là:
+ Lab out nếu ra từ 8:00PM - 11:59PM thì trả ra kết quả là: 8:00AM ngày hôm sau.
+ Lab out nếu ra từ 00:00AM đến 07:59AM thì trả ra kết quả là 8:00AM cùng ngày.
Nên em đã chỉnh lại hàm anh gửi một chút là:

F4=IF(MOD(G4,1)>20/24,INT(G4)+1+8/24,IF(MOD(G4,1)<7/24,INT(G4)+8/24,G4))
** Do em không hiểu được ý nghĩa +4/3 nên mượn code của anh Befaint để chèn vào. Hy vọng anh không khó chịu vì em dở mảng này lắm nên lụm lặt mỗi chỗ một ít....**
Và kèm theo là xét thêm một lần nữa điều kiện ở Report out (ô F5) vì lỡ như phải để giờ ra là sang 8:00AM ngày hôm sau mà trùng vào ngày nghỉ thì toi.
(Vẫn là file đính kèm nếu như anh có hứng thú hoặc không hiểu em đang nói gì )

 

[Nhoc247]

Chốt lại là chưa biết em đã giải quyết triệt để vụ này chưa vì còn cần phải test lại nhiều trường hợp nhưng vẫn một lần nữa cảm ơn hai anh @befaint và @quocgiacan nhiều lắm.
Có cơ hội cho em mời hai anh uống nước để cảm ơn nhé.
Chúc hai anh một ngày tốt lành.

P/s: À, sau vụ này phải tranh thủ sắp xếp thời gian đi học thêm excel thôi, thấy bản thân yếu quá cỡ