Tính thời gian làm việc thực tế (2 người xem)

[le tin]

Chào các bạn
Tôi có bài toán về tìm thời gian thực tế như file đính kèm , mong có sự giúp đỡ
Cảm ơn

 

[BNTT]

Em xin góp một cách giải:

Dựa vào dữ liệu của bác, em đặt 3 Name để kiểm tra dữ liệu ngày giờ như sau:

• Test1 = Sheet1!F$4 + Sheet1!F$3/24
• Test2 = (AND(HOUR(Test1)>11.5,HOUR(Test1)<13.5))*2/24 + (OR(HOUR(Test1)>17,HOUR(Test1)<7))*16.5/24
• Test3 = (WEEKDAY(Test2+Test1)=7)*44/24 - (WEEKDAY(Sheet1!F$4)=7)*2/24

Công thức để tính thời gian dự kiến kết thúc (F5:H5)

= Test1 + Test2 + Test3​

Công thức để tính thời gian làm thực tế (F7:H7)

=(F6-F4)*24 - (AND(HOUR(F6)>13.5, HOUR(F4)<11.5)*2 + (INT(F6)-INT(F4))*16.5 + (WEEKDAY(F6)=2)*11)​

Nhờ bác kiểm tra lại giúp em xem có đúng hay không.

----------------------
BÀI NÀY CHƯA CHÍNH XÁC. XIN PHÉP GỠ BỎ FILE ĐÍNH KÈM.
BNTT.

 

[ThuNghi]

Em xin góp một cách giải:

Dựa vào dữ liệu của bác, em đặt 3 Name để kiểm tra dữ liệu ngày giờ như sau:

• Test1 = Sheet1!F$4 + Sheet1!F$3/24
• Test2 = (AND(HOUR(Test1)>11.5,HOUR(Test1)<13.5))*2/24 + (OR(HOUR(Test1)>17,HOUR(Test1)<7))*16.5/24
• Test3 = (WEEKDAY(Test2+Test1)=7)*44/24 - (WEEKDAY(Sheet1!F$4)=7)*2/24

Công thức để tính thời gian dự kiến kết thúc (F5:H5)

= Test1 + Test2 + Test3​

Công thức để tính thời gian làm thực tế (F7:H7)

=(F6-F4)*24 - (AND(HOUR(F6)>13.5, HOUR(F4)<11.5)*2 + (INT(F6)-INT(F4))*16.5 + (WEEKDAY(F6)=2)*11)​

Nhờ bác kiểm tra lại giúp em xem có đúng hay không.

Bác Tường làm hay quá, khi nào Bác chỉ giúp tôi hàm hour này với. Bữa giờ xem mà không biết cách làm giúp Bác Le Tin.
Bác giải thích hộ số *44/24
Cám ơn Bác nhiều!

 

[ptm0412]

44 là số giờ từ 11:30 thứ bảy đến 7:30 sáng thứ 2
chia 24 là giờ quy ra ngày (học của ngocmaipretty), ngược lại nhân 24 là quy ngày ra giờ
nhân 44/24 là nếu WEEKDAY(Test2+Test1)=7) = "giờ kết thúc là nhằm vào thứ 7" thì cộng thêm 44 giờ

Giải thích hộ BNTT, nhưng hình như còn sai:
- test1 + test2 trúng ngày chủ nhật thì sao?
- test1 + test2 trúng ngày thứ bảy, nhưng vào buổi sáng thì sao?
- trừ 2 giờ để làm gì?

Chắc phải cầu cứu lại Ms. NgocMai!

 

[BNTT]

Bác Tường làm hay quá, khi nào Bác chỉ giúp tôi hàm hour này với. Bữa giờ xem mà không biết cách làm giúp Bác Le Tin.
Bác giải thích hộ số *44/24
Cám ơn Bác nhiều!

Để em diễn Nôm cho bác nghe nhá. Trước tiên bác phải nắm được 2 cái này:

• Khi muốn chuyển đổi một con số (1 tiếng, 2 tiếng rưỡi... kiểu nói) ra dạng thời gian cho Excel nó hiểu, thì mình chia cho 24. Bởi vì 1 giờ trong Excel có nghĩa là 1/24 (ngày), 12 giờ = 12/24 = nữa ngày.
• Còn khi muốn chuyển một số ở dạng thời gian của Excel thành con số kiểu tiếng Việt, là làm ngược lại í, thì lại nhân cho 24. Bác xem công thức tính thời gian làm việc thực tế, em lại đi nhân cho 24 đó.

Test1 =Sheet1!F$4 + Sheet1!F$3/24

Lấy thời gian bắt đầu cộng với định mức quy định, để tính ra thời gian kết thúc (chưa tính giờ nghỉ, ngày nghỉ gì hết).
Do ở F3 đang là dạng con số, nên phải chia cho 24 để nó có thể cộng được với F4 (đang là dạng thời gian).
Tuy nhiên, kết quả này, còn phải cộng thêm giờ nghỉ trưa, giờ đi nhậu buổi chiều... cho nên phải có thêm cái Test 2.​

Test2 =(AND(HOUR(Test1)>11.5, HOUR(Test1)<13.5))*2/24 + (OR(HOUR(Test1)>17, HOUR(Test1)<7))*16.5/24

Mấy con số 11.5, 13.5, 17, 7 chắc bác đoán được là gì chớ?
Con số 2/24 (2 tiếng) chính là thời gian nghỉ buổi trưa (từ 11 giờ đến 13 giờ)
Con số 16.5/24 (16 tiếng rưỡi) là thời gian từ sau 5 giờ chiều cho đến 7 giờ sáng hôm sau.
Công thức này đem cái kết quả của Test1 đi dò, nếu rơi vào mấy khoảng thời gian nghỉ, thì phải cộng thêm tương ứng.
Nhưng cái công thức này cũng chưa chính xác, bởi nó chưa tính đến chuyện chiều thứ 7 thì đi Hòn đất chứ không đi làm, còn ngày Chủ Nhật thì nghỉ nguyên ngày, ở nhà... ngủ. Bởi thế nên mới có cái Test3.​

Test3 =(WEEKDAY(Test2+Test1)=7)*44/24 - (WEEKDAY(Sheet1!F$4)=7)*2/24

Cái này là cái em nhức đầu nhất. Phải hết chừng 2 điếu con mèo mới nghĩ ra cái trò kiểm tra kết quả của Test1 cộng với Test2, nếu nó rơi vào thứ 7, thì phải cộng thêm giờ làm chiều thứ 7 và ngày nghỉ CN...
Thế là em xòe ngón tay ra đếm: Ngày CN có 24 tiếng, ngày thứ Bảy làm có 4 tiếng buổi sáng à, vậy dư 20 tiếng, lấy 24+20 ra 44 tiếng. Bác hiểu con số 44 ở đâu ra rồi chứ.
Nhưng chưa hết, thứ Bảy thì nghỉ từ 11 giờ 30, đâu có nghỉ trưa, trong khi mình đã trừ cái chuyện nghỉ trưa ở Test2 rồi, nên phải thêm một chuyện, nếu như ngày bắt đầu làm là ngày thứ 7, thì phải trừ bớt đi 2 tiếng nghỉ trưa.​

Cuối cùng, cộng hết 3 cái Test này lại, là ra kết quả thời gian hoàn thành theo dự kiến.

Phù... khi chơi thì không thấy gì. Mà phải kể lại chuyện đã chơi như thế nào, sao mà nó khó thế!

 

[BNTT]

44 là số giờ từ 11:30 thứ bảy đến 7:30 sáng thứ 2
chia 24 là giờ quy ra ngày (học của ngocmaipretty), ngược lại nhân 24 là quy ngày ra giờ
nhân 44/24 là nếu WEEKDAY(Test2+Test1)=7) = "giờ kết thúc là nhằm vào thứ 7" thì cộng thêm 44 giờ

Giải thích hộ BNTT, nhưng hình như còn sai:
- test1 + test2 trúng ngày chủ nhật thì sao?
- test1 + test2 trúng ngày thứ bảy, nhưng vào buổi sáng thì sao?
- trừ 2 giờ để làm gì?

Chắc phải cầu cứu lại Ms. NgocMai!

Hu hu... Đúng là chưa chính xác.
Bài này không dùng hàm HOUR được rồi! Vì Hour nó hổng tính được cái 30 phút!
Để đó, em sẽ làm lại. Chơi MOD chứ hổng chơi HOUR nữa. Cũng hông thèm cầu viện Ms. Ngọc Mai.
Nói với cái lão Cheettit là sau khi làm lại em sẽ tự xóa bài này.

 

[ptm0412]

Mấy con số 11.5, 13.5, 17, 7 chắc bác đoán được là gì chớ?

Hình như 7,5 mới đúng thì phải?

 

[BNTT]

Bài đã gửi hôm qua không chính xác. Xin thành thật cáo lỗi cùng bác Letin và bác ThuNghi. Em xin gửi lại bài khác:

Em đặt lại 4 Name để kiểm tra dữ liệu ngày giờ như sau:

• Test1: Dùng để tính nhẩm thời gian hoàn thành, chưa tính thời gian nghỉ và ngày nghỉ
  
  = Sheet1!F$4 + Sheet1!F$3/24​
  
  
• Test2: Dùng để tính thời gian nghỉ trưa (2 tiếng) và thời gian nghỉ tối (16.5 tiếng)
  
  = (AND(Test1-INT(Test1)>11.5/24, Test1-INT(Test1)<13.5/24))*2/24 + (OR(Test1-INT(Test1)>17/24, Test1-INT(Test1)<7.5/24))*16.5/24​
  
  
• Test3: Dùng để kiểm tra xem kết quả có rơi vào chiều thứ 7, hay vào Chủ Nhật hay không, và kiểm tra xem thời gian bắt đầu có phải là sáng thứ 7 hay không. Nếu có thì cộng thêm khoảng thời gian tương ứng.
  
  =OR(AND(WEEKDAY(Test2+Test1)=7, (Test2+Test1)-INT(Test2+Test1)>11.5/24), WEEKDAY(Test2+Test1)=1)*44/24 - AND(WEEKDAY(Sheet1!F$4)=7, (Test2+Test1)-INT(Test2+Test1)>11.5/24)*2/24​
  
  
• Test4: Dùng để kiểm tra xem kết quả cuối cùng có rơi vào trong giờ nghỉ trưa hoặc buổi tối hay không. Nếu có thì cộng thêm khoảng thời gian tương ứng.
  
  = IF((Test1+Test2+Test3)-INT(Test1+Test2+Test3)>17/24, 16.5/24, IF((Test1+Test2+Test3)-INT(Test1+Test2+Test3)>11.5/24, 2/24, 0))​

Công thức để tính thời gian dự kiến kết thúc (F5:H5)

= Test1 + Test2 + Test3 + Test4​

Công thức để tính thời gian làm thực tế (F7:H7) vẫn như cũ:

=(F6-F4)*24 - (AND(HOUR(F6)>13.5, HOUR(F4)<11.5)*2 + (INT(F6)-INT(F4))*16.5 + (WEEKDAY(F6)=2)*11)​

Nhờ các bác kiểm tra lại giúp em xem có đúng hay không.

 

[le tin]

Cảm ơn bạn BNTT ,nhưng chưa ổn ,Ví dụ nhập 41.5 thì phải cách đúng 1 tuần sau (vì 1 tuần làm 41.5 giờ) chứ

 

[BNTT]

Cảm ơn bạn BNTT ,nhưng chưa ổn ,Ví dụ nhập 41.5 thì phải cách đúng 1 tuần sau (vì 1 tuần làm 41.5 giờ) chứ

Dạ đúng là như vậy. Chưa chính xác. Em đang nghiên cứu lại nó, vì quả thật là ... hơi khó nhai!

 

[le tin]

Phần tính ra thời gian dự định thì khó , mình chỉ mới có trong ý tưởng thôi
Ý định là : Cứ lấy thời gian bắt đầu cọng cho 3 (chẳng hạng vậy) , đến khi quá 11.30 thứ 7 thì cọng vào 40 để đến T2 , cho đến bằng hoặc quá Định mức thì dừng lại .
Không biết ý đó có giúp bạn đỡ đau đầu không ,(mình nghĩ ghi ra code có dễ hơn không ?)
Cảm ơn bạn

 

[ndu96081631]

Dạ đúng là như vậy. Chưa chính xác. Em đang nghiên cứu lại nó, vì quả thật là ... hơi khó nhai!

Bài toán dạng này không phải là KHÓ, mà là CỰC KHÓ
Có 1 bài toán gần tương tự:
- Cho trước ngày bắt đầu
- Cho trước tổng số ngày
- Tính ngày kết thúc (trừ CN và các ngày nghĩ)
Hoặc 1 bài toán thuận:
- Cho trước giờ bắt đầu
- Cho trước giờ kết thúc
- Tính tổng số giờ làm việc (trừ các giờ nghĩ)
Nhưng bài tính thời gian của tác giã còn khó hơn ---> Khó ở điểm nó là 1 bài toán ngược... và các khoảng nghĩ nằm lung tung
Tuy nhiên các bạn cứ thử với bài toán về NGÀY, nếu làm ra thì đó là cơ sở để giãi quyết bài toán về GIỜ

 

[BNTT]

Bài toán dạng này không phải là KHÓ, mà là CỰC KHÓ

Quả đúng là Cực Khó!
Em nghĩ rằng chắc không dùng công thức nổi, giả như có nổi đi nữa thì chắc cái công thức này sẽ dài khủng khiếp, và phải rất nhiều công thức thì mới có thể chơi nổi!
Em có ý tưởng này, dùng VBA. Nhưng em thì không giỏi về VBA, nên xin nêu ra ý tưởng thôi, các bác xem có khả thi không thì làm dùm...

Lấy thời gian bắt đầu + thời gian dự định sẽ hoàn thành công việc, ra kết quả là A chẳng hạn. Rồi dùng vòng lặp để kiểm tra kết quả A:

• Nếu A rơi vào khoảng nghỉ trưa (tử 11:30 đến 13:30) thì cộng thêm 2 tiếng. Cho ra A1.
• Nếu A1 rơi vào khoảng nghỉ chiều (từ 17:00 đến 7:30 ngày hôm sau) thì cộng thêm 14,5 tiếng. Cho ra A2.
• Nếu A2 rơi vào trưa thứ 7 (sau 11:30) thì cộng thêm 44 tiếng, cho ra A3.
• Rồi lại quay vòng tiếp, nếu A3 rơi vào khoảng nghỉ trưa thì lại cộng thêm 2 tiếng
• V.v...
• Cho tới khi nào cái kết quả này nằm trong khoảng thời gian chấp nhận được (rơi vào trong giờ hành chánh) thì dừng lại.

(Các điều kiện kiểm tra trên có thể còn thiếu... em chỉ ví dụ thôi)

Liệu có khả thi không ạ ?

 

[ndu96081631]

Quả đúng là Cực Khó!
Em nghĩ rằng chắc không dùng công thức nổi, giả như có nổi đi nữa thì chắc cái công thức này sẽ dài khủng khiếp, và phải rất nhiều công thức thì mới có thể chơi nổi!
Em có ý tưởng này, dùng VBA. Nhưng em thì không giỏi về VBA, nên xin nêu ra ý tưởng thôi, các bác xem có khả thi không thì làm dùm...

Lấy thời gian bắt đầu + thời gian dự định sẽ hoàn thành công việc, ra kết quả là A chẳng hạn. Rồi dùng vòng lặp để kiểm tra kết quả A:

• Nếu A rơi vào khoảng nghỉ trưa (tử 11:30 đến 13:30) thì cộng thêm 2 tiếng. Cho ra A1.
• Nếu A1 rơi vào khoảng nghỉ chiều (từ 17:00 đến 7:30 ngày hôm sau) thì cộng thêm 14,5 tiếng. Cho ra A2.
• Nếu A2 rơi vào trưa thứ 7 (sau 11:30) thì cộng thêm 44 tiếng, cho ra A3.
• Rồi lại quay vòng tiếp, nếu A3 rơi vào khoảng nghỉ trưa thì lại cộng thêm 2 tiếng
• V.v...
• Cho tới khi nào cái kết quả này nằm trong khoảng thời gian chấp nhận được (rơi vào trong giờ hành chánh) thì dừng lại.

(Các điều kiện kiểm tra trên có thể còn thiếu... em chỉ ví dụ thôi)

Liệu có khả thi không ạ ?

Tôi thì nghĩ vầy:
- Lấy thời gian bắt đầu + TG dự kiến + với 1 số gia nào đó (số gia này dựa vào khoãng nghĩ nào mà ta cho là lớn nhất, theo tôi đó là ngày CN) ---> Ra được 1 tổng mới, đăt nó là M chẳng hạn
- Dùng For quét i từ 1 đến M
- Xét Start + i xem chúng có lọt vào các khoảng nghĩ không? Nếu không lọt thi "nhớ" số i này vào 1 biến, chẳng hạn là K (nếu lọt vào khoảng nghĩ thì ta vẩn + i nhưng không "nhớ")
- Cứ thế tiếp diển ---> Đến khi nào tổng số "nhớ" K đúng bằng với TG dự kiến thì OK "con gà đen" ---> Thoát ---> Finish = Start + i
Đại khái là vậy ---> Nhưng vẫn "tê" (vì GIỜ GIẤC xem bộ khó ăn hơn NGÀY THÁNG) ---> Và sẽ còn khó khăn hơn nếu phải xét đến các ngày LỂ

 

[le tin]

Tôi gởi file nhờ các bạn xem giúp tối ưu hóa , mới tập viết code ,nhưng thấy nhiều IF quá , và chưa phát hiện sai .
Tôi đã làm tìm thời gian thực tế (Chưa biết ổn không), còn thời gian dự định chưa làm được , mời các bạn động não tiếp . Cảm ơn

 

[ThuNghi]

Tôi gởi file nhờ các bạn xem giúp tối ưu hóa , mới tập viết code ,nhưng thấy nhiều IF quá , và chưa phát hiện sai .
Tôi đã làm tìm thời gian thực tế (Chưa biết ổn không), còn thời gian dự định chưa làm được , mời các bạn động não tiếp . Cảm ơn

Em làm thử 1 function EstEndDate dự kiến ngày kết thúc. Anh xem thử có OK, em làm tiếp phần kia.
Cú pháp EstEndTime(Ngaydau, số giờ)

Function EstEndTime(NgayDau As Variant, SoPhut As Long)
Dim NgayCuoi As Variant, iPhut As Long, IntervalType As String
IntervalType = "n"
SoPhut = SoPhut * 60
If Weekday(NgayDau) = 1 Then Exit Function 'xem lai cac dieu kien ve t7, CN
If Not IsDate(NgayDau) Then Exit Function
If SoPhut = 0 Then
    EstEndTime = NgayCuoi
    Exit Function
End If
iPhut = 30
Do While Not iPhut = SoPhut + 30
    NgayCuoi = DateAdd(IntervalType, 30, NgayDau)
        If Weekday(NgayDau) <> 7 And Hour(NgayCuoi) = 11 And Minute(NgayCuoi) = 30 And iPhut < SoPhut Then
            NgayDau = DateAdd(IntervalType, 120, NgayCuoi)
        ElseIf Weekday(NgayDau) <> 7 And Hour(NgayCuoi) = 17 And Minute(NgayCuoi) = 0 And iPhut < SoPhut Then
             NgayDau = DateAdd(IntervalType, 14 * 60 + 30, NgayCuoi)
        ElseIf Weekday(NgayDau) = 7 And Hour(NgayCuoi) = 11 And Minute(NgayCuoi) = 30 And iPhut < SoPhut Then
             NgayDau = DateAdd(IntervalType, 44 * 60, NgayCuoi)
        Else
            NgayDau = NgayCuoi
        End If
        iPhut = iPhut + 30
Loop
EstEndTime = NgayCuoi
End Function

 

[le tin]

Hình như là số giờ chứ không phải số phút
Và chỉ OK đối với số giờ chẵn .

 

[ThuNghi]

Hình như là số giờ chứ không phải số phút
Và chỉ OK đối với số giờ chẵn .

Chạy sub sau thì OK mà chưa hiểu sao chuyển sang UDF thì sai ở giờ end la 11h30

Sub EndTime()
Dim NgayDau, NgayCuoi, iPhut As Long
With Application
    .Calculation = xlCalculationManual
End With
Dim SoPhut As Long
Dim IntervalType As String
Sheet2.Select
IntervalType = "n"
NgayDau = Cells(2, 2)
SoPhut = Cells(1, 2) * 60
If Weekday(NgayDau) = 1 Then Exit Sub 'xem lai thu sau
iPhut = 30
Do While Not iPhut = SoPhut + 30
    NgayCuoi = DateAdd(IntervalType, 30, NgayDau)
        If Weekday(NgayDau) <> 7 And Hour(NgayCuoi) = 11 And Minute(NgayCuoi) = 30 And iPhut < SoPhut Then
            NgayDau = DateAdd(IntervalType, 120, NgayCuoi)
        ElseIf Weekday(NgayDau) <> 7 And Hour(NgayCuoi) = 17 And Minute(NgayCuoi) = 0 And iPhut < SoPhut Then
             NgayDau = DateAdd(IntervalType, 14 * 60 + 30, NgayCuoi)
        ElseIf Weekday(NgayDau) = 7 And Hour(NgayCuoi) = 11 And Minute(NgayCuoi) = 30 And iPhut < SoPhut Then
             NgayDau = DateAdd(IntervalType, 44 * 60, NgayCuoi)
        Else
            NgayDau = NgayCuoi
        End If
        iPhut = iPhut + 30
Loop
Cells(2, 3) = NgayCuoi
End Sub

Anh chạy thử sub sau.

 

[le tin]

Chưa ổn lắm nhưng không hiểu ý nghĩa mấy chữ trong câu này nên cũng chưa biết nên sửa thế nào : NgayDau = DateAdd(IntervalType, 120, NgayCuoi)
Mình nói rõ thêm là Giờ định mức ấy có thể là : 10 ph ,15 ph , ... 30 ph,35 ph...1 g ,..

 

[ThuNghi]

Chưa ổn lắm nhưng không hiểu ý nghĩa mấy chữ trong câu này nên cũng chưa biết nên sửa thế nào : NgayDau = DateAdd(IntervalType, 120, NgayCuoi)
Mình nói rõ thêm là Giờ định mức ấy có thể là : 10 ph ,15 ph , ... 30 ph,35 ph...1 g ,..

Bạn nghiên cứu hàm DateAdd nó cũng tựa như hàm Datedif.
Em chưa hiểu Định mức công việc(g) này là nhập theo dạng gì, trước mắt làm theo số. Cụ thể 1.5 là 1h30, 1.1 là 1h6'
Bác thử lại UDF này xem sao, do code chiều sai ở khai biến.
Em cho 1 block là 30', Bác có thể sửa lại thành 5, 10...

Function EndTime(NgayDau As Variant, SoPhut As Double)
Dim NgayCuoi As Variant, iPhut As Double, Block As Long
SoPhut = SoPhut * 60 'xem lai neu 1h5' thi nhap so the nao'
If Weekday(NgayDau) = 1 Then Exit Function 'xem lai cac dieu kien ve t7, CN'
If Not IsDate(NgayDau) Then Exit Function
Block = 30
If SoPhut = 0 Then
    EndTime = NgayCuoi
    Exit Function
End If
iPhut = Block
Do While Not iPhut = SoPhut + Block
    NgayCuoi = DateAdd("n", Block, NgayDau)
        If Weekday(NgayDau) <> 7 And Hour(NgayCuoi) = 11 And Minute(NgayCuoi) = 30 Then
            NgayDau = DateAdd("n", 120, NgayCuoi)
        ElseIf Weekday(NgayDau) <> 7 And Hour(NgayCuoi) = 17 And Minute(NgayCuoi) = 0 Then
             NgayDau = DateAdd("n", 14 * 60 + 30, NgayCuoi)
        ElseIf Weekday(NgayDau) = 7 And Hour(NgayCuoi) = 11 And Minute(NgayCuoi) = 30 Then
             NgayDau = DateAdd("n", 44 * 60, NgayCuoi)
        Else
            NgayDau = NgayCuoi
        End If
        iPhut = iPhut + Block
Loop
EndTime = NgayCuoi
End Function

Nếu Block là 5,10,15, 30, 45,60' thì dễ hơn, còn block 12' thì chưa biết cách làm. Nên nhập vào thời gian dm là số phút thay vì 1.1, 1.2...Khó chia mà phải làm tròn. Hay là Bác đưa tình huống khác.