Tìm ngày giờ kết thúc dự kiến (2 người xem)

[vanthinh3101]

Bây giờ là sáng sớm Chủ nhật bên đó, tuần này thì tôi hết ngồi không, vã lại thời gian mấy ngày nay chắc các bạn cũng đã nghiền ngẫm, trăn trở với nó rồi, nên cũng là lúc tôi cũng phải đưa ra cách xử lý của mình. Tôi sẽ dùng cách đại trà để ai cũng dễ nắm bắt vấn đề.

Các bạn cũng sẽ như tôi lúng túng trong việc làm sao xác định được số ngày cộng thêm mà không bị rơi vào ngày CN và lễ, thấy rắc rối nhưng không phải vậy, và giống như tôi gợi ý với bạn @vanthinh3101 ở bài #12

Vd: Ngày bắt đầu còn 1.5 tiếng, thời gian thực hiện cần 20 tiếng, vậy thời gian còn lại là = 20 - 1.5 = 18.5 tiếng. Vì 1 ngày làm việc cố định 8 tiếng, vậy cần 3 ngày cộng thêm (8*3=24 tiếng -> bội số của 8) để thực hiện kết thúc, trong đó 2 ngày đầu mất 16 tiếng, vậy giờ kết thúc là 2.5 tiếng. Ngày kết thúc bắt đầu từ lúc 8h00 + 2.5 tiếng = 10h30 là mốc giờ kết thúc.

Muốn tìm bội số của 8 thì tôi dùng hàm Ceiling( 'Thời gian còn lại' /8 ,1). Vậy, đặt bội số này vào hàm:
=WORKDAY.INTL( 'Ngày bắt đầu', 'bội số của 8', 11, 'Ngày CN/ lễ' ) thì tìm được ngày cần thêm để kết thúc công việc mà không rơi vào CN và ngày lễ.

Tôi thực hiện cả hai phiên bản trước và từ 365 để các bạn trước 365 có thể tham khảo:

1/ Công thức trước 365:
a. Ngày giờ điều chỉnh:

=WORKDAY.INTL(B6,N(MOD(B6,1)*24>=17),11,$J$6:$J$12)+IF(MOD(B6,1)*24<17,TEXT(MAX(MOD(B6,1)*24,8),"[<12];[>13];13"),8)/24

b.Ngày giờ kết thúc:

=WORKDAY.INTL(D6,CEILING((C6-(17-ROUND(MOD(D6,1)*24,6)-(ROUND(MOD(D6,1)*24,6)<13)))/8,1),11,$J$6:$J$12)+IF(MOD(C6-(17-ROUND(MOD(D6,1)*24,6)-(ROUND(MOD(D6,1)*24,6)<13)),8),8+MOD(C6-(17-ROUND(MOD(D6,1)*24,6)-(ROUND(MOD(D6,1)*24,6)<13)),8)+(MOD(C6-(17-ROUND(MOD(D6,1)*24,6)-(ROUND(MOD(D6,1)*24,6)<13)),8)>4),17)/24

2/ Công thức 365:
a. Ngày giờ điều chỉnh:

=LET(gio,MOD(B6,1)*24,WORKDAY.INTL(B6,N(gio>=17),11,$J$6:$J$12)+IF(gio<17,TEXT(MAX(gio,8),"[<12];[>13];13"),8)/24)

b.Ngày giờ kết thúc:

=LET(Gcl,LET(gio,ROUND(MOD(D6,1)*24,6),C6-16+gio-(gio>12)),Glt,CEILING(Gcl/8,1),Gdc,Gcl-Glt*8,WORKDAY.INTL(D6,Glt,11,$J$6:$J$12)+(16+Gdc+(8+Gdc>4))/24)

Các bạn xem đồ biểu, thuật toán tôi có nêu trong file kèm

Chúc các bạn ngày vui

Sau khi nghiền ngẫm, em đã tư duy ngược 1 chút anh ạ.
Nếu anh lấy 17h của Ngày bắt đầu làm mốc thì em sẽ tìm cách lấy 8h Ngày bắt đầu làm mốc.
Sau khi "vái tứ phương" bao gồm cả người và AI đã ra được 1 công thức mới:

E2=WORKDAY.INTL(D6,INT((MOD(D6,1)*24-8+C6)/8),11,$J$6:$J$10)+8/24+MOD(MOD(D6,1)*24-8+C6,8)/24+N(MOD(MOD(D6,1)*24-8+C6,8)>4)/24
Fill xuống

Em cũng học anh phân tích ra như sau:
- MOD(D6,1)*24-8+C6 --> Công thức tính ra số giờ đã sử dụng của Ngày bắt đầu và Thời hạn thực hiện.
- Sử dụng INT(.../8) --> tính ra phần nguyên để đưa vào tham số days trong Workday.Intl
- Workday.Intl(...) --> trả về kết quả là ngày làm việc tiếp theo, với mốc là 0h --> chính vì thế phải cộng thêm 8/24 để được thời gian bắt đầu là 8h
- MOD(....,8)/24 --> tính ra phần dư sau khi chia cho 8 để cộng thêm.
- N(MOD(...,8)>4)/24 --> phần dư mà lớn hơn 4h --> thời gian thực hiện sẽ vượt quá 12h -->phải cộng thêm 1h nghỉ trưa

Hại não thật anh ạ

 

[huuthang_bd]

MOD(D6,1)*24-8

Sai chỗ này nên đi cả bài.

 

[vanthinh3101]

Sai chỗ này nên đi cả bài.

Thật là thiếu sót, đúng là có trường hợp bị sai anh ạ.
Em nhờ anh chỉ giúp em để có hướng khắc phục ạ.
Em thì chỉ đang tư duy đơn giản là: Lấy phần giờ của ngày bắt đầu rồi nhân với 24 sẽ ra số giờ dạng số rồi trừ đi 8 (8h dạng số).

 

[huuthang_bd]

Thật là thiếu sót, đúng là có trường hợp bị sai anh ạ.
Em nhờ anh chỉ giúp em để có hướng khắc phục ạ.
Em thì chỉ đang tư duy đơn giản là: Lấy phần giờ của ngày bắt đầu rồi nhân với 24 sẽ ra số giờ dạng số rồi trừ đi 8 (8h dạng số).

Sai do bạn không trừ thời gian từ 12h00 đến 13h00 khi giờ bắt đầu lớn hơn 13h00.

 

[Phan Thế Hiệp]

Hại não thật anh ạ

Đáng khen em trai! Việc suy luận thì tốt cho não chứ có hại gì đâu em!

Như anh @huuthang_bd có chỉ điểm thì công thức của em sẽ là vầy:
=WORKDAY.INTL(D6,INT((MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6)/8),11,$J$6:$J$10)+(8+MOD(MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6,8)+(MOD(MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6,8)>4))/24

Tuy vậy, còn 1 trường hợp em chưa tính đến:

Vd: ô D7= T3 29/04/2025 15:30, giờ thực hiện cần: C7= 1.5 tiếng, nếu tính tay thì nó sẽ là T3 29/04/2025 17:00 kết thúc công việc, kết quả từ công thức của em là T6 02/05/2025 8:00. Anh thấy là do đoạn:
INT((MOD(D7,1)*24-8-(MOD(D7,1)*24>12)+C7)/8)

Em thử tính tay nha, sẽ biết lý do:
--> MOD(D7,1)*24-8-(MOD(D7,1)*24>12) = 15.5 - 8 - (15.5>12) = 6.5 tiếng
--> 6.5 + C7 = 6.5 + 1.5 = 8 tiếng
--> INT( 8 tiếng/8) = 1 (ngày)

Đem vào công thức WORKDAY.INTL(), lấy mốc ngày tại D7 tính tới, cộng thêm 1 ngày (không rơi vào CN, lễ), do vậy mình chưa kịp xét 8 tiếng này vẫn còn trong ngày hay qua ngày khác rồi nó cứ mặc nhiên tính thêm lên. Cũng giống vậy cho C7= 9.5 tiếng, 17.5 tiếng....

Anh thấy em ngày càng tiến bộ trong việc tự mình tìm hướng giải quyết vấn đề, thật lòng anh rất vui.

Chúc em ngày vui, bình an

(Tb: anh bên đạo nên chúc bình an cho nhau là việc thiện lành nhất)

 

[vanthinh3101]

Đáng khen em trai! Việc suy luận thì tốt cho não chứ có hại gì đâu em!

Như anh @huuthang_bd có chỉ điểm thì công thức của em sẽ là vầy:
=WORKDAY.INTL(D6,INT((MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6)/8),11,$J$6:$J$10)+(8+MOD(MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6,8)+(MOD(MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6,8)>4))/24

Tuy vậy, còn 1 trường hợp em chưa tính đến:

Vd: ô D7= T3 29/04/2025 15:30, giờ thực hiện cần: C7= 1.5 tiếng, nếu tính tay thì nó sẽ là T3 29/04/2025 17:00 kết thúc công việc, kết quả từ công thức của em là T6 02/05/2025 8:00. Anh thấy là do đoạn:
INT((MOD(D7,1)*24-8-(MOD(D7,1)*24>12)+C7)/8)

Em thử tính tay nha, sẽ biết lý do:
--> MOD(D7,1)*24-8-(MOD(D7,1)*24>12) = 15.5 - 8 - (15.5>12) = 6.5 tiếng
--> 6.5 + C7 = 6.5 + 1.5 = 8 tiếng
--> INT( 8 tiếng/8) = 1 (ngày)

Đem vào công thức WORKDAY.INTL(), lấy mốc ngày tại D7 tính tới, cộng thêm 1 ngày (không rơi vào CN, lễ), do vậy mình chưa kịp xét 8 tiếng này vẫn còn trong ngày hay qua ngày khác rồi nó cứ mặc nhiên tính thêm lên. Cũng giống vậy cho C7= 9.5 tiếng, 17.5 tiếng....

Anh thấy em ngày càng tiến bộ trong việc tự mình tìm hướng giải quyết vấn đề, thật lòng anh rất vui.

Chúc em ngày vui, bình an

(Tb: anh bên đạo nên chúc bình an cho nhau là việc thiện lành nhất)

Em cám ơn anh @Phan Thế Hiệp và anh @huuthang_bd nhiều ạ.
Vậy là công thức đã được hoàn chỉnh

 

[Phan Thế Hiệp]

Vậy là công thức đã được hoàn chỉnh

Chưa em ạ!

Em chỉ đọc phần trên bài anh viết, còn phần dưới từ chỗ "Tuy vậy,..." em không đọc , mà phần đó để xử lý còn phải thêm lắm bước "truân chuyên", khà khà

Chúc em ngày vui

 

[lehuong1409]

Chưa em ạ!

Em chỉ đọc phần trên bài anh viết, còn phần dưới từ chỗ "Tuy vậy,..." em không đọc , mà phần đó để xử lý còn phải thêm lắm bước "truân chuyên", khà khà

Chúc em ngày vui

Em chào anh ạ
Em thấy anh em bàn luận hăng say mà em thì nhìn công thức xong chắc lăng lẽ đi ra thôi ạ. Em vào để chào anh và cũng để ghim lại một bài viết tâm huyết để sau này em vào ngâm cứu sau ạ
Chúc anh một ngày mới tốt lành ạ

 

[Phan Thế Hiệp]

Em chào anh ạ
Em thấy anh em bàn luận hăng say mà em thì nhìn công thức xong chắc lăng lẽ đi ra thôi ạ. Em vào để chào anh và cũng để ghim lại một bài viết tâm huyết để sau này em vào ngâm cứu sau ạ
Chúc anh một ngày mới tốt lành ạ

Chào em,

Cũng đã lâu lắm rồi anh em mình mới trao đổi với nhau, em và gia đình vẫn vui khỏe.

Nguyên do anh đào lại bài này là do một người em trai thân thiết, anh rất quý, gửi cho anh một đề tài "hóc búa" cho anh cơ hội thư giản, thời may anh vẫn còn sức, gắng gượng vượt qua "tường lửa" đó. Tâm trạng vẫn còn say sưa vì bài hay quá, nên anh tìm bài tương tự nhưng dễ hơn nhiều lần để chia sẻ với anh em là bài này.

Lâu lâu nói chuyện với em anh rất vui.

Chúc em và gia đình ngày vui khỏe

 

[Phan Thế Hiệp]

Vậy là công thức đã được hoàn chỉnh

Như anh nói ở bài #, khi tham gia đóng góp bài sẽ nhận được sự góp ý thật lòng, đến khi nào sự hoàn thiện từ kết quả của công thức đem lại sẽ mang cho anh em mình 1 cảm giác vui lâng lâng khó lột tả. Làm việc này, lại gợi cho anh nhớ kỷ niệm cũ hồi còn là "ma mới", bước chân vào diễn đàn như "ếch ngồi đáy giếng" đã lâu, nay "lên được thành giếng" thấy cả 1 vùng trời rộng lớn mà bỡ ngỡ, toàn gặp "quái chiêu" khác lạ so với những gì sách vở ghi. Rồi bầu bạn đấu công thức với nhiều anh em: anh @HieuCD, @leoheocon... gặp "ma cũ" anh @huuthang_bd học toàn chiêu "độc", trong số đó có 1 em lúc nào cũng góp ý "công thức sai chỗ này", "sai chỗ kia" lắm lúc làm anh cáu bực trong lòng, nhưng ngẫm nghĩ lại "chính người chỉ cho mình chỗ sai mới là người bạn, người thầy chân chính", từ đó về sau anh cẩn thận hơn khi viết ra công thức, và anh rất ghét cái thói nói "làm đại" khi giải bài cho anh em. Kể từ đó anh rất quý người anh em chỉ chỗ mình sai, mà có lần anh gọi đùa là "Thanh tra CP", đó chính là @befaint.... Kể lể như vậy để mong em xem các góp ý dưới đây là tấm lòng của anh dành cho em nói riêng và anh em khác nói chung.

Các công thức anh dẫn giải sau là anh "chôm" y xì giải thuật của anh @huuthang_bd (anh chỉ có 'công' viết lại và dúi cho em xài, phải nói rõ vậy nha! )

Anh đã nhắc đoạn công thức:
INT((MOD(D7,1)*24-8-(MOD(D7,1)*24>12)+C7)/8)
làm chệch số ngày thực hiện cần có, lẽ ra nó phải trừ 8 (tiếng) thì mới đúng trong cùng ngày, nhưng nếu trừ như vậy sẽ ảnh hưởng đến những ngày khác không cùng ngày!?

Thay vì trực tiếp hàm INT(n/8), em dùng cách gián tiếp theo công thức:
INT(n/8)= (n - MOD(n,8))/8 (công thức này có hướng dẫn trong hàm Mod() của Microsoft)
 Gọi:

• gio=MOD(D7,1)*24
• gth=gio-8-(gio>12)+C7
• gcl=MOD(gth-1%,8)+1% (-1% để khi gth=8, thì Mod() không bằng 0, rồi +1% lại thành 8)
• Vậy (gth-gcl)/8 chính là INT(n/8) nói trên. Vd: (lấy lại vd bài #25) gio=15.5; gth= 15.5-8-(15.5>12)+1.5=8 ; gcl= Mod(8-1%,8)+1%=8 ==> (gth-gcl)/8= (8-8)/8=0 tức nó sẽ lấy cùng ngày khi đưa vào WORKDAY.INTL().
• Vd khác: giả sử giờ thực hiện là C7=2.5 tiếng, gio=15.5, gth= 15.5-8-(15.5>12)+2.5=9 ; gcl= Mod(9-1%,8)+1%=1 ==> (gth-gcl)/8= (9-1)/8=1 tức nó sẽ lấy thêm 1 ngày khi đưa vào WORKDAY.INTL().

Công thức sau cùng sẽ là:

=WORKDAY.INTL(D6,ROUND(((MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6)-(MOD((MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6)-1%,8)+1%))/8,0),11,$J$6:$J$10)+(8+MOD((MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6)-1%,8)+1%+(MOD((MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6)-1%,8)+1%>4))/24

Hoặc: (365)

=LET(gio,MOD(D6,1)*24,gth,gio-8-(gio>12)+C6,gcl,MOD(gth-1%,8)+1%,WORKDAY.INTL(D6,ROUND((gth-gcl)/8,0),11,$J$6:$J$12)+(8+gcl+(gcl>4))/24)

Bây giờ công thức mới hoàn chỉnh

Chúc em ngày thiệt vui