Trong một khoảng thời gian có bao nhiêu ngày thứ Bảy, Chủ Nhật,... ? (5 người xem)

[BNTT]

Nhân bài toán của Hoàng Danh, tôi mới chế ra một cái công thức để đếm xem trong một khoảng thời gian nào đó có bao nhiêu ngày thứ Bảy, bao nhiêu ngày Chủ Nhật.

Đây là công thức đếm ngày thứ Bảy của tôi:

=IF(OR(AND(WEEKDAY(Ngaydau)>4,WEEKDAY(Ngaycuoi)=7),AND(WEEKDAY(Ngaydau)>5,WEEKDAY(Ngaycuoi)<5)),INT((Ngaycuoi-Ngaydau+1)/7)+1,INT((Ngaycuoi-Ngaydau+1)/7))

Với Ngaydau và Ngaycuối là hai ô chứa ngày đầu và ngày cuối trong khoảng thời gian cần tính.

Công thức này trên hơi dài, nhưng có thể dùng để "fill down" cho nhiều hàng, tôi đã dùng để kiểm tra số ngày thứ Bảy có trong mỗi tháng ở nhiều năm khác nhau.

Nếu như chỉ cần nhập 1 ô là Ngày Đầu, và 1 ô là Ngày cuối, thì đặt Name cho gọn hơn:

• NgayDau = ô chứa giá trị ngày đầu của khoảng thời gian cần tính
• NgayCuoi = ô chứa giá trị ngày cuối của khoảng thời gian cần tính
• DAU = WEEKDAY(Ngaydau)
• CUOI = WEEKDAY(Ngaycuoi)
• TUAN = INT((Ngaycuoi-Ngaydau+1)/7)

Thì để đếm số ngày thứ Bảy có trong một khoảng thời gian, chỉ cần nhập ngắn như sau:

=IF(OR(AND(DAU>4, CUOI=7), AND(DAU>5, CUOI<5)), TUAN+1, TUAN)​

Tương tự, công thức để đếm số ngày Chủ Nhật có trong một khoảng thời gian là:

=IF(OR(AND(DAU=1, CUOI<7), AND(DAU>5, CUOI<6)), TUAN+1, TUAN)​

Nhờ các bạn góp ý thêm, bằng cách khác, hoặc làm cho nó ngắn hơn.
Dùng công thức hoàn toàn, không dùng VBA hoặc hàm UDF nha.

 

[ndu96081631]

Anh ơi, nó đơn giãn lắm chứ có dài dòng thế đâu
Công thức tổng quát, đếm ngày nào cũng được:

Tính số ngày thứ hai:   =INT((Y-X-WEEKDAY(Y,2)+8)/7)
Tính số ngày thứ ba:    =INT((Y-X-WEEKDAY(Y-1,2)+8)/7)
Tính số ngày thứ tư:     =INT((Y-X-WEEKDAY(Y-2,2)+8)/7)
Tính số ngày thứ năm: =INT((Y-X-WEEKDAY(Y-3,2)+8)/7)
Tính số ngày thứ sáu:   =INT((Y-X-WEEKDAY(Y-4,2)+8)/7)
Tính số ngày thứ bảy:   =INT((Y-X-WEEKDAY(Y-5,2)+8)/7)
Tính số ngày chủ nhật:  =INT((Y-X-WEEKDAY(Y-6,2)+8)/7)

Với:
X = ngày đầu
Y = Ngày cuối
Xem tại đây:
http://www.giaiphapexcel.com/forum/showthread.php?t=2515&page=3

 

[ndu96081631]

Nhân bài toán của Hoàng Danh, tôi mới chế ra một cái công thức để đếm xem trong một khoảng thời gian nào đó có bao nhiêu ngày thứ Bảy, bao nhiêu ngày Chủ Nhật.
.

Với bài của hoangdanh282vn thì anh nên dùng mãng hiệu quả hơn, vì anh không chỉ đếm Thứ 7, CN mà còn phải xét xem LỂ có nằm trong những ngày ấy không!
Ví dụ:
1> Tạo 1 name liệt kê các ngày từ đầu đến cuối:

Ngay =Ngaycuoi-1+ROW(INDIRECT("1:"&Ngaycuoi-Ngaydau+1))

2> Định nghĩa ngày lể là những ngày nào:

Le ={"0101","3004","0105","0209"}

Từ đây anh sẽ tính được rất nhiều thứ
1> Tính xem có bao nhiêu ngày lể trùng thứ 7, CN

{=SUM((TEXT(IF(WEEKDAY(Ngay,2)<6,,Ngay),"ddmm")=Le)*1)}

2> Tính xem có bao nhiêu ngày từ thứ 2 đến thứ 6:

{=COUNT(IF(WEEKDAY(Ngay,2)>5,"",Ngay))}

3> Tính xem có bao nhiêu ngày lể trùng vào những ngày từ thứ 2 đến thứ 6:

{=SUM((TEXT(IF(WEEKDAY(Ngay,2)>5,"",Ngay),"ddmm")=Le)*1)}

Vân vân và vân vân

 

[bigbigworld]

Mình có một bài toán:
Hôm nay là ngày 05/07/2008, Mình muốn biết sau 45 ngày nữa là là ngày nào?Nhưng phải trừ những ngày lễ và chủ nhật ra.
Theo bạn ndu96081631 thì phải dùng mảng nhưng mà kiến thức về mảng của mình không được vững lắm bạn nào giải giúp mình nhé.Có thể cho mình file ví dụ càng tốt. Thanks

 

[anhtuan1066]

Mình có một bài toán:
Hôm nay là ngày 05/07/2008, Mình muốn biết sau 45 ngày nữa là là ngày nào?Nhưng phải trừ những ngày lễ và chủ nhật ra.
Theo bạn ndu96081631 thì phải dùng mảng nhưng mà kiến thức về mảng của mình không được vững lắm bạn nào giải giúp mình nhé.Có thể cho mình file ví dụ càng tốt. Thanks

Câu hỏi này cũng rất hay!
Bạn tham khảo file đính kèm, chỉ cần sửa lại ngày bắt đầu là ổn
Cần tìm ngày thứ 45 thì =INDEX(DATE,45,1)
(Gợi ý thôi, bạn còn phải sửa lại đôi chút trong name nhé)

 

[ThuNghi]

Câu hỏi này cũng rất hay!
Bạn tham khảo file đính kèm, chỉ cần sửa lại ngày bắt đầu là ổn
Cần tìm ngày thứ 45 thì =INDEX(DATE,45,1)
(Gợi ý thôi, bạn còn phải sửa lại đôi chút trong name nhé)

Hôm nay là ngày 05/07/2008, Mình muốn biết sau 45 ngày nữa là là ngày nào?Nhưng phải trừ những ngày lễ và chủ nhật ra.

Thú thật là mình cũng chưa hiểu vậy endDate là ngày nào.
Bạn addIns thêm Analysis ToolPak, sau đó dùng hàm Workday(NgayDau,SoNgay,NL)
Còn không dùng 1 code đếm.

 

[ndu96081631]

Ở Việt Nam không dùng hàm WORKDAY được đâu, vì Excel tính ngày làm việc từ thứ 2 đến thứ 6, nghĩ ngày thứ 7 và CN

 

[BNTT]

Bác Thunghi ơi, bác chế ra một cái hàm UDF đi... Em gợi ý hướng thôi:
Tính từ một ngày, cho nó chạy tới bao nhiêu ngày đó, vửa chạy vừa đếm vừa xem chừng, nếu như mà gặp ngày thứ nghỉ lễ, Chủ Nhật gì đó thì bỏ qua không đếm. Chạy cho tới khi nào đủ số ngày cần có thì dừng lại ?
Cái khó là phải lập ra một danh sách mấy cái ngày nghỉ lễ, rồi đưa vào trong đó. Em thì dốt khoản này lắm.
Bác nghiên cứu thử nha.

 

[ndu96081631]

Làm thử 1 UDF, các bạn góp ý nha:

Function Ngaylamviec(Batdau As Date, SN As Integer, Le As Date) As Date
  Dim i, k As Integer
  For i = 1 To SN * 2
     If Weekday(Batdau + i) <> 1 And Batdau + i <> Le Then
        k = i
        If k >= SN Then Exit For
     End If
  Next
  Ngaylamviec = Batdau + k
End Function

(chưa hoàn hảo lắm)

 

[ThuNghi]

Làm thử 1 UDF, các bạn góp ý nha:

Function Ngaylamviec(Batdau As Date, SN As Integer, Le As Date) As Date
  Dim i, k As Integer
  For i = 1 To SN * 2
     If Weekday(Batdau + i) <> 1 And Batdau + i <> Le Then
        k = i
        If k >= SN Then Exit For
     End If
  Next
  Ngaylamviec = Batdau + k
End Function

(chưa hoàn hảo lắm)

Le phải là 1 range chớ không thể là date, lễ có hơn ngày. Bác Test lại thử

 

[ndu96081631]

Le phải là 1 range chớ không thể là date, lễ có hơn ngày. Bác Test lại thử

Chính xác, nhưng nếu ngon hơn nữa Le là 1 mãng chứa các String thì càng tốt!
Ví dụ tôi gõ 30/4, 1/5 thì nó tự biết Le là 30 tháng 4 và 1 tháng 5 bất kể năm nào!
Ngoài ra còn vụ nghĩ lể âm lịch nữa, khá rắc rối!
Bởi vậy code trên chỉ là mở màn, các bạn hảy góp thêm cho hoàn chỉnh hơn

 

[anhtuan1066]

Nhờ các bạn kiểm tra lại file này nhé
ANH TUẤN

 

[BNTT]

Nhờ các bạn kiểm tra lại file này nhé
ANH TUẤN

Cho em hỏi cái này:

DK=IF(((COUNTIF(NN,DATE))+(WEEKDAY(DATE)=1)),"",ROW(INDIRECT("1:"&Sheet1!$B$2*2)))​

Tại sao phải mở nhiều ngoặc đơn thế? Bớt đi 2 cái có ảnh hưởng gì không?

 

[anhtuan1066]

Cho em hỏi cái này:

DK=IF(((COUNTIF(NN,DATE))+(WEEKDAY(DATE)=1)),"",ROW(INDIRECT("1:"&Sheet1!$B$2*2)))​

Tại sao phải mở nhiều ngoặc đơn thế? Bớt đi 2 cái có ảnh hưởng gì không?

Do quá trình làm công thức này, tôi phải test từng công thức một, khi ráp vào nó dư
Bạn bỏ bớt đi, có sao đâu!
Thêm nữa: Do nghĩ Tết âm lịch liên tục trong 6 ngày, nên có lẻ cần phải sửa lại name, chổ nào *2 thì sửa lại *6
(đã sửa lại trong file đính kèm ở bài trên)

 

[Mr Okebab]

Bác Thunghi ơi, bác chế ra một cái hàm UDF đi... Em gợi ý hướng thôi:
Tính từ một ngày, cho nó chạy tới bao nhiêu ngày đó, vửa chạy vừa đếm vừa xem chừng, nếu như mà gặp ngày thứ nghỉ lễ, Chủ Nhật gì đó thì bỏ qua không đếm. Chạy cho tới khi nào đủ số ngày cần có thì dừng lại ?
Cái khó là phải lập ra một danh sách mấy cái ngày nghỉ lễ, rồi đưa vào trong đó. Em thì dốt khoản này lắm.
Bác nghiên cứu thử nha.

Việc chạy như vậy thì giống như quá trình brute force quá. Tuy nhiên được cái bao giờ cũng đúng.

Nếu ngày không xa nhau lắm (khoảng 100 ngày gì đó - nghe ghê quá) thì dùng nó, còn nếu cách xa nhau quá thì nên dùng các thuật toán rút gọn đi bác ạ.

Thân!

 

[bigbigworld]

Thú thật là mình cũng chưa hiểu vậy endDate là ngày nào.

45 ngày sau là ngày 27/08/2008, có nghĩa là mình bắt đầu đếm từ 05/07/2008 là ngày thứ 1, 06/07 là 2 .... 27/08/2008 là ngày thứ 45, trong đó mình đã trừ ra các ngày chủ nhật và ngày lễ (30/04 or 02/09 ....nói chung là các ngày lễ được nghỉ làm theo luật qui định)

 

[ndu96081631]

Thử thêm 1 chiêu nữa:

Option Explicit
Function Ngaycuoi(Ngaydau As Date, SN As Integer, Le As Range) As Date
   Dim TextN As String
   Dim i, K, DK As Integer
   For i = 1 To SN * 6
      TextN = Day(Ngaydau + i) & "/" & Month(Ngaydau + i)
      DK = Application.WorksheetFunction.CountIf(Le, TextN)
      If DK = 0 And Weekday(Ngaydau + i) <> 1 Then K = K + 1
      If K = SN Then K = i: Exit For
   Next
   Ngaycuoi = Ngaydau + K
End Function

Code này thử sơ qua chưa phát hiện lổi (nhờ các bạn test lại)
Tuy nhiên tôi vẩn còn gặp khó khăn trong chuyện ngày Lể là âm lịch
Điểm dở trong code này là: không biết phải quét i = 1 đến bao nhiêu là vừa đủ
--------------------------------------
Nhân đây cho tôi hỏi thêm 1 chút:
Nhờ các bạn chỉ hộ tôi hàm nào trong VBA tương đương với hàm TEXT của công thức Excel?
Tôi muốn chuyển đổi dử liệu theo cách sau:
=TEXT(ngày tháng năm, "dd/mm")
Nhưng trong VBA lại không biết dùng hàm nào?

 

[OverAC]

Format(DateSerial(2008, 1, 1), "mmm dd,yyyy") = "jan 01, 2008"

=TEXT(Ngày tháng năm, "dd/mm") ~ format(ngày tháng năm, "dd/mm")

 

[phamduylong]

Nhân đây cho tôi hỏi thêm 1 chút:
Nhờ các bạn chỉ hộ tôi hàm nào trong VBA tương đương với hàm TEXT của công thức Excel?
Tôi muốn chuyển đổi dử liệu theo cách sau:
=TEXT(ngày tháng năm, "dd/mm")
Nhưng trong VBA lại không biết dùng hàm nào?

Bạn dùng hàm Format
ngay=Format(ngaythang,"đ/mm")

Function Ngaycuoi(Ngaydau As Date, SN As Integer, Le As Range) As Date
Nếu ngày đầu=07/07/2008, số ngày làm việc=3, không có ngày lễ thì hàm Ngaycuoi cho kết quả là 10/07/2008.
Đúng phải là ngày 09/07/2008 vì ngày 07/07 là ngày làm việc thứ nhất (hay ngày đầu không tính ?)

 

[ndu96081631]

Bạn dùng hàm Format
ngay=Format(ngaythang,"dd/mm")

Function Ngaycuoi(Ngaydau As Date, SN As Integer, Le As Range) As Date
Nếu ngày đầu=07/07/2008, số ngày làm việc=3, không có ngày lễ thì hàm Ngaycuoi cho kết quả là 10/07/2008.
Đúng phải là ngày 09/07/2008 vì ngày 07/07 là ngày làm việc thứ nhất (hay ngày đầu không tính ?)

Cái đó cũng không quan trọng lắm anh ơi! Việc tính ngày nào là ngày thứ nhất là tùy quan điểm của mổi người... Trong file của em không xem ngày bắt đầu là ngày 1
Nhân đây lại hỏi thêm vấn đề nữa:
Tôi phát triển thêm hàm để tính được cả âm lịch. Tôi quy định rằng trong vùng LỂ nếu gõ theo dạng TEXT thì đó là lể dương lịch, nếu gõ theo dạng DATE thì đó là lể âm lịch!
Thế nhưng kết quả lại chưa chính xác! Ví dụ ngày 6/2/2008, 7/2/2008 và 8/2/2008 là lể âm lịch, vậy nếu ngày bắt đầu là ngày 5/2/2008, số ngày là 1 thì ngày kết thúc lý ra phải là ngày 9/2/2008.
Thế mà nó lại cho kết quả = 11/2/2008
Code của nó như sau:

Option Explicit
Function Ngaycuoi(Ngaydau As Date, SN As Integer, Le As Range) As Date
   Dim TextN As String
   Dim iNgay As Date
   Dim i, K, DK1, DK2 As Integer
   For i = 1 To SN * 6
      iNgay = Ngaydau + i
      TextN = Format(iNgay, "d/m")
      DK1 = Application.WorksheetFunction.CountIf(Le, TextN)
      DK2 = Application.WorksheetFunction.CountIf(Le, iNgay)
      If DK1 = 0 And DK2 = 0 And Weekday(iNgay) <> 1 Then K = K + 1
      If K = SN Then K = i: Exit For
   Next
   Ngaycuoi = Ngaydau + K
End Function

Nhờ các bạn xem file đính kèm và cho biết tại sao lại có sai sót này!