Tính số ngày công làm việc giữa hai khoảng thời gian

[DOSNET]

Mình có một câu hỏi này, mời các bạn cùng tham gia tìm giải pháp :
Tính số ngày công (2 buổi/ngày) làm việc của một nhân viên từ ngày 01/06/1980 đến ngày 29/02/2008.
Biết rằng ngày CN nghỉ, ngày thứ bảy làm 1/2 ngày, có 4 ngày nghỉ lễ là 01/01,30/04,01/05,02/09

Vấn đề này thấy vậy mà không đơn giản lắm. Những ngày 01/01, 30/04... nếu rơi vào T7, CN thứ tính làm sao. Và có cho dùng VBA không.
Theo tôi bài này dùng VBA là hay nhất
Còn 01/06/1980 - 29/02/2008 > 10.000 ngày
Nếu sumproduct(--(weekday(indirect(...))<>1),--(weekday(indirect(...))<>7),--(day()<>1),--...) với indirect 10.000 dòng thì không cách gì mà nhanh được.

Do ta đếm số ngày T7 và CN giữa hai khoảng thời gian cho dù có rơi vào các ngày lễ vẫn được tính để trừ đi. (coi như nghỉ bù) nên vấn đề này vẫn đc giải quyết ổn thỏa bác ạh !

Được bác à. Đây là giải pháp vì thế tất nhiên là VBA sẽ được ứng dụng nhiều. Tuy nhiên có công thức thì càng ok.
Công thức so với công thức, VBA so với VBA

*Giải pháp của mình là :
Tống số buổi = (tổng số ngày giữa hai khoảng thời gian)*2 +1
Trong đó: phải trừ đi số buổi :
- trừ (tổng số ngày chủ nhật)*2
- trừ tổng số ngày Thứ 7
- Trừ ngày lễ (4*2)
Việc chính là đếm được có bao nhiêu ngày T7, CN trong khoảng thời gian đã cho !

-----------------
Do lỗi mạng nên trật tự các bài bị đảo lộn.
Xin phép sắp xếp lại.
BNTT

 

[BNTT]

Còn giải pháp của mình là:
Cái này, dùng hàm NETWORKDAYS() thì sẽ tính được số ngày làm việc giữa hai khoảng thời gian, trừ đi các ngày thứ Bảy, Chủ Nhật và mấy cái ngày nghỉ lễ.
Ở đây có thêm chuyện làm 1/2 ngày thứ Bảy, do đó hướng giải quyết là tìm xem trong khoảng từ 1/6/1980 đến 29/02/2008 có bao nhiêu ngày thứ Bảy, rồi lấy con số này nhân với 1/2, sẽ có một kết quả.
Xong lấy kết quả đó cộng với kết quả của NETWORKDAYS() sẽ có đáp số cuối cùng.

 

[hoangdanh282vn]

Giải pháp thì chắc là nhiều, chúng ta hãy gửi bài của mình lên xem cách ai ngắn gọn và chạy nhanh nhất, không dùng name hay cột phụ, test thử cho 100 nhân viên

 

[cadafi]

Mình có một câu hỏi này, mời các bạn cùng tham gia tìm giải pháp :
Tính số ngày công (2 buổi/ngày) làm việc của một nhân viên từ ngày 01/06/1980 đến ngày 29/02/2008.
Biết rằng ngày CN nghỉ, ngày thứ bảy làm 1/2 ngày, có 4 ngày nghỉ lễ là 01/01,30/04,01/05,02/09

Góp ý thêm:
Mấu chốt vấn đề có thể là phải tạo mảng một chiều chứa tất cả các phần tử là ngày giữa hai mốc thời gian. Ví dụ: MyRng={"01/06/1980","02/06/1980",.....,"28/02/2008","29/02/2008"}
Còn tất cả các vấn đề khác thì đơn giản rồi.

 

[148btx]

Tôi hay tính thời gian bao gồm cả thứ bẩy và chủ nhật như sau:
Thời gian bắt đầu (tháng/ngày/năm) tại ô A1
Thời gian kết thúc (tháng/ngày/năm) tại ô A2

Kết quả ở ô A3:
=DATEDIF(A1;A2;"Y") & " năm " & DATEDIF(A1;A2;"M")-DATEDIF(A1;A2;"Y")*12 & " tháng " & DATEDIF(A1;A2;"MD") & " ngày"
Chi tiết hơn thì kết quả số năm trong ô A4: =DATEDIF(A1;A2;"Y")
Kết quả số tháng: A5=DATEDIF(A1;A2;"M")-A4*12
Kết quả số ngày: A6=DATEDIF(B15;B16;"MD")

Còn tính chính xác số ngày thì dùng hàm NETWORKDAYS() thì sẽ tính được số ngày làm việc giữa hai khoảng thời gian, trừ đi các ngày thứ Bảy, Chủ Nhật và mấy cái ngày nghỉ lễ như bạn BNTT nói.

 

[dongdv]

Công thức của mình như sau: (A1 là ngày bắt đầu, A2 là ngày kết thúc):
=(A2-A1)-(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(VALUE(A1)&":"&VALUE(A2))),1)=1)))-(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(VALUE(A1)&":"&VALUE(A2))),1)=7))/2)-(SUMPRODUCT(--((DAY(ROW(INDIRECT(VALUE(A1)&":"&VALUE(A2))))=1)*MONTH(ROW(INDIRECT(VALUE(A1)&":"&VALUE(A2))))=1))+SUMPRODUCT(--((DAY(ROW(INDIRECT(VALUE(A1)&":"&VALUE(A2))))=30)*MONTH(ROW(INDIRECT(VALUE(A1)&":"&VALUE(A2))))=4))+SUMPRODUCT(--((DAY(ROW(INDIRECT(VALUE(A1)&":"&VALUE(A2))))=1)*MONTH(ROW(INDIRECT(VALUE(A1)&":"&VALUE(A2))))=5))+SUMPRODUCT(--((DAY(ROW(INDIRECT(VALUE(A1)&":"&VALUE(A2))))=2)*MONTH(ROW(INDIRECT(VALUE(A1)&":"&VALUE(A2))))=9)))

Do lười đặt tên nên ct hơi dài. Mình không dùng Networkdays vì phải có thêm cột phụ, và sẽ hơi khó tính nếu ngày lễ trùng vào t7, CN.

Kết quả cuối cùng là 7852.5 ngày!

 

[DOSNET]

Tôi hay tính thời gian bao gồm cả thứ bẩy và chủ nhật như sau:
Thời gian bắt đầu (tháng/ngày/năm) tại ô A1
Thời gian kết thúc (tháng/ngày/năm) tại ô A2

Kết quả ở ô A3:
=DATEDIF(A1;A2;"Y") & " năm " & DATEDIF(A1;A2;"M")-DATEDIF(A1;A2;"Y")*12 & " tháng " & DATEDIF(A1;A2;"MD") & " ngày"
Chi tiết hơn thì kết quả số năm trong ô A4: =DATEDIF(A1;A2;"Y")
Kết quả số tháng: A5=DATEDIF(A1;A2;"M")-A4*12
Kết quả số ngày: A6=DATEDIF(B15;B16;"MD")

Còn tính chính xác số ngày thì dùng hàm NETWORKDAYS() thì sẽ tính được số ngày làm việc giữa hai khoảng thời gian, trừ đi các ngày thứ Bảy, Chủ Nhật và mấy cái ngày nghỉ lễ như bạn BNTT nói.

Thực tế bài này không đơn giản như bạn nghĩ...hãy bắt tay vào làm và cho biết kết quả. Thân !

 

[hoangdanh282vn]

Mình có một câu hỏi này, mời các bạn cùng tham gia tìm giải pháp :
Tính số ngày công (2 buổi/ngày) làm việc của một nhân viên từ ngày 01/06/1980 đến ngày 29/02/2008.
Biết rằng ngày CN nghỉ, ngày thứ bảy làm 1/2 ngày, có 4 ngày nghỉ lễ là 01/01,30/04,01/05,02/09

Mình xin được nói thêm.
Do đây là giải pháp nên các bạn có thể làm thế này tùy thích, miễn sao chạy đúng là được, kể cả VBA.
Nếu ngày thứ 7 trùng vào ngày lễ thì coi như không đi làm. còn vấn đề nghỉ bù thì chưa quan tâm tới

 

[BNTT]

Tôi xin làm kiểu củ chuối nhé, không phải là cách làm hay đâu, nhưng có thể dùng để kiểm tra cái đáp số của các bạn:

Mình có một câu hỏi này, mời các bạn cùng tham gia tìm giải pháp :
Tính số ngày công (2 buổi/ngày) làm việc của một nhân viên từ ngày 01/06/1980 đến ngày 29/02/2008.
Biết rằng ngày CN nghỉ, ngày thứ bảy làm 1/2 ngày, có 4 ngày nghỉ lễ là 01/01,30/04,01/05,02/09

Tôi làm nguyên một dãy (làm một ngày rồi fill down xuống) có tên là NGAY, từ A1:A10135, trong đó:

• A1="01/6/1980" (đặt tên là Ngaydau)
  
  
• A10135="29/2/2008" (đặt tên là Ngaycuoi)
  
  
• Công thức tính số ngày làm việc trong khoảng thời gian từ ngày 01/06/1980 đến ngày 29/02/2008 (đã trừ đi thứ Bảy và Chủ Nhật):
  
  = NETWORKDAYS(Ngaydau, Ngaycuoi) = 7240 (ngày)
  
  
  
• Công thức đếm các ngày thứ Bảy trong khoảng thời gian đó:
  
  {=SUM(IF(WEEKDAY(NGAY)=7,1,0))} = 1447 (ngày)
  
  
  
• Công thức đếm các ngày 01/01 (mà không phải là thứ Bảy hay Chủ Nhật):
  
  =SUMPRODUCT((DAY(NGAY)=1)*(MONTH(NGAY)=1)*(WEEKDAY(NGAY)<>7)*(WEEKDAY(NGAY)<>1)) = 20 (ngày)
  
  
  
• Công thức đếm các ngày 30/4 (mà không phải là thứ Bảy hay Chủ Nhật):
  
  =SUMPRODUCT((DAY(NGAY)=30)*(MONTH(NGAY)=4)*(WEEKDAY(NGAY)<>7)*(WEEKDAY(NGAY)<>1)) = 19 (ngày)
  
  
  
• Công thức đếm các ngày 01/5 (mà không phải là thứ Bảy hay Chủ Nhật):
  
  =SUMPRODUCT((DAY(NGAY)=1)*(MONTH(NGAY)=5)*(WEEKDAY(NGAY)<>7)*(WEEKDAY(NGAY)<>1)) = 19 (ngày)
  
  
  
• Công thức đếm các ngày 02/9 (mà không phải là thứ Bảy hay Chủ Nhật):
  
  =SUMPRODUCT((DAY(NGAY)=2)*(MONTH(NGAY)=9)*(WEEKDAY(NGAY)<>7)*(WEEKDAY(NGAY)<>1)) = 20 (ngày)

Ghép hết chúng lại với nhau:

= 7240 + 1447/2 - (20+19+19+20) = 7885.5 ngày​

--------------------
@ Bạn dongdv: Bạn làm gần đúng rồi đó.
Chỉ cần để ý thêm có những ngày 1/1, 30/4, 1/5, và 2/9 trùng với ngày Chủ Nhật hoặc Thứ Bảy, do đó bạn đã trừ "nhiều" hơn là thực tế (ví dụ, bạn đã trừ một ngày 01/1/1984, rồi lại trừ thêm 1 ngày Chủ Nhật 01/1/1984 nữa, thành ra trừ mất 2 ngày), và kết quả là đáp số của bạn (7852.5) ít hơn của tôi.


-------------
Nói thêm tí nữa, theo tôi nghĩ, nếu không dùng VBA, thì bài này nên dùng những ô riêng chứa từng công thức nhỏ như tôi trình bày ở trên.
Vì nếu ghép thành một công thức duy nhất thì cũng được (như của bạn dongdv đã làm) nhưng nếu là để chia sẻ cho nhau, thì hóa ra lại là đánh đố nhau thôi.
Nếu không có môt trình độ nào đó nhất định, thì không thể nào hiểu được một cái công thức dài chừng nửa mét (!), huống hồ là cái công thức duy nhất cho bài toán này. Đó là chưa tính cái khoản nghỉ bù à! Và những thứ như: một năm thì có nhiêu ngày phép, rồi còn Tết Nguyên Đán, còn Giỗ Tổ Hùng Vương, v.v... Đã lảm thì làm tới nơi, mà nếu nhét hết tất cả vào trong một công thức thì có mà chết!

"Đường nào thì cũng đến La Mã", có đường gần, đường xa, đường dễ, đường khó... Tôi thì hay chọn đường dễ đi, dễ quay lại khi lạc đường, chứ ít khi tôi chọn đường tắt lắm, vì đường tắt thì có ngắn thật, nhưng lỡ lạc đường muốn quay lại thì chẳng biết đâu mà lần!

 

[DOSNET]

- Có thể đếm số ngày thứ 7 và CN như sau cho đơn giản hơn
Nhập ngày đầu vào A1 và ngày cuối vào B1 Đặt các name :

DAU=WEEKDAY($A2) {ngày đầu}

CUOI=WEEKDAY($B2) {ngày cuối}

NGHI=$B2-$A2+1-NETWORKDAYS($A2,$B2) {tổng số ngày nghỉ bao gồm cả T7 và CN}

T7=IF(AND(DAU<>1,CUOI=7),(NGHI+1)/2,IF(AND(DAU=1,CUOI<>7),(NGHI-1)/2,NGHI/2)) { số ngày T7}

CN= NGHI-THU7 {số ngày CN}

 

[BNTT]

DOSNET cho hỏi tí.
Vậy kết quả là bao nhiêu ngày công?
Kết quả nằm ở nơi cột C à?
Nghĩa là nó = ($B2-$A2-CN-NLE)*2-THU7 = 15709 ?
Sao nhiều thế?
Từ ngày 01/6/1980 đến ngày 29/2/2008 chỉ có 10134 ngày thôi mà?

 

[DOSNET]

Đó là số buổi bác ah, lấy (số buổi) /2 ra ngày công !

 

[BNTT]

Đó là số buổi bác ah, lấy (số buổi) /2 ra ngày công !

Vậy, 15709/2 = 7854.5, ít hơn cái đáp án của mình...
Hình như DOSNET cũng quên tính ngày lễ trùng ngày với Thử Bảy, Chủ Nhật rồi...

 

[DOSNET]

Vậy, 15709/2 = 7854.5, ít hơn cái đáp án của mình...
Hình như DOSNET cũng quên tính ngày lễ trùng ngày với Thử Bảy, Chủ Nhật rồi...

Nếu trùng thì là nghỉ bù mà bác !

 

[BNTT]

Nếu trùng thì là nghỉ bù mà bác !

Bài này, tác giả (Hoàng Danh) nói rằng chưa tính đến chuyện nghỉ bù!

 

[DOSNET]

- Nếu thực tế là phải được nghỉ bù, bài của em cũng phải sửa lại một tý về cách tính ngày lễ. Do không sử dụng mảng nên phải tính theo cách sau :
- Tính tổng số năm trong khoảng thời gian
- Tính số ngày nghỉ lễ năm đầu (xác định thời điểm bắt đầu nằm trong đoạn nào của các ngày lễ)
- Tính số ngày nghỉ lễ năm cuối. ( xác định thời điểm kết thúc nằm trong đoạn nào của các ngày lễ)
--> Số ngày lễ = (Tổng số năm -2)*4 + số ngày lễ năm đầu + Số ngày lễ năm cuối
Tuy nhiên nếu vận dụng được mảng sẽ tính chi tiết và chính xác hơn nhưng chắc chắn tốc độ sẽ rùa bò ( bài của e với 1000 dòng ko thấy độ trễ tính toán)
------------------
P/s : Khó quá... ngồi chờ sung rụng vậy !

 

[cadafi]

Bài này, tác giả (Hoàng Danh) nói rằng chưa tính đến chuyện nghỉ bù!

Nếu thêm điều kiện này vào nữa thì rắc rối thêm đây! Nhìn đơn giản mà không đơn giản nhỉ!

 

[BNTT]

- Nếu thực tế là phải được nghỉ bù, bài của em cũng phải sửa lại một tý về cách tính ngày lễ. Do không sử dụng mảng nên phải tính theo cách sau :
- Tính tổng số năm trong khoảng thời gian
- Tính số ngày nghỉ lễ năm đầu (xác định thời điểm bắt đầu nằm trong đoạn nào của các ngày lễ)
- Tính số ngày nghỉ lễ năm cuối. ( xác định thời điểm kết thúc nằm trong đoạn nào của các ngày lễ)
--> Số ngày lễ = (Tổng số năm -2)*4 + số ngày lễ năm đầu + Số ngày lễ năm cuối
Tuy nhiên nếu vận dụng được mảng sẽ tính chi tiết và chính xác hơn nhưng chắc chắn tốc độ sẽ rùa bò ( bài của e với 1000 dòng ko thấy độ trễ tính toán)
------------------
P/s : Khó quá... ngồi chờ sung rụng vậy !

Hic... làm kiểu của mình đi, chia nhỏ ra, tính từng phần, rồi cộng kết quả lại.
Nhanh lắm à, không rùa đâu.
Mà đọc lại kỹ đề thì bài của mình cũng chưa đúng
Bỏ sót mất cái này: "Nếu ngày lễ là thứ Bảy thì nghỉ luôn, không có làm 1/2 ngày" !
Để mình sửa lại cái đã.

 

[DOSNET]

Hic... làm kiểu của mình đi, chia nhỏ ra, tính từng phần, rồi cộng kết quả lại.
Nhanh lắm à, không rùa đâu.
Mà đọc lại kỹ đề thì bài của mình cũng chưa đúng
Bỏ sót mất cái này: "Nếu ngày lễ là thứ Bảy thì nghỉ luôn, không có làm 1/2 ngày" !
Để mình sửa lại cái đã.

Nếu được show dữ liệu ra thì bài của bác là tương đối chính xác. Tính cho 500 nhân viên thì không đủ cột để nhập ! Khó ở chỗ nhập liệu ! chỉ nhập ngày đầu và ngày cuối-->ra kết quả !

 

[BNTT]

Nếu được show dữ liệu ra thì bài của bác là tương đối chính xác. Tính cho 500 nhân viên thì không đủ cột để nhập ! Khó ở chỗ nhập liệu ! chỉ nhập ngày đầu và ngày cuối-->ra kết quả !

Ôi... lại không đọc kỹ đề bài nữa rồi.

Tính số ngày công (2 buổi/ngày) làm việc của một nhân viên từ ngày 01/06/1980 đến ngày 29/02/2008.
Biết rằng ngày CN nghỉ, ngày thứ bảy làm 1/2 ngày, có 4 ngày nghỉ lễ là 01/01,30/04,01/05,02/09

Với cái đề bài như thế này thì nếu muốn tính lương cho 500 người, chỉ việc lấy số ngày công nhân với 500 rồi nhân với tiền thôi!

Quay lại chuyện lúc nãy, do tác giả thêm cái này:

Mình xin được nói thêm.
Do đây là giải pháp nên các bạn có thể làm thế này tùy thích, miễn sao chạy đúng là được, kể cả VBA.
Nếu ngày thứ 7 trùng vào ngày lễ thì coi như không đi làm. còn vấn đề nghỉ bù thì chưa quan tâm tới

Nên mình phải thêm một cái SUMPRODUCT nữa để tính những ngày lễ trùng với ngày thứ Bảy:

Ví dụ cho ngày 1/1:

= SUMPRODUCT((DAY(NGAY)=1)*(MONTH(NGAY)=1)*(WEEKDAY(NGAY)=7))​

Cả 4 ngày: 1/1, 30/4, 1/5 và 2/9, mỗi cái đều cho ra kết quả là 4, nghĩa là có 4 x 4 = 16 ngày nghỉ lễ trùng với thứ Bảy.

Vậy, đáp số đúng là (các bạn chịu khó dò với bài lúc nãy của tôi nghe):

Lấy số ngày làm của hàm NETWORKDAYS (=7240), cộng thêm tổng số ngày thứ bảy mà trừ bớt đi 16 rồi chia 2 (= (1447 - 16)/2 = 715.5), rồi trừ đi tổng số ngày nghỉ rơi vào những ngày thường trong tuần (= 20 + 19 + 19 + 20 = 78):

7240 + 715.5 - 78 = 7877.5​

Có đúng không Hoàng Danh ơi?