Dùng hàm hoặc code nào để tính được số ngày Chủ Nhật trong một quãng T/G bất kỳ

2 Responses

1. hands says:

   31/01/2023 at 6:59 pm

   Trong file tôi gởi có hàm songay(ngay1,ngay2,thu)
   Ô A1 Nhập dữ liệu ngày: 01/01/2007
   Ô B1 Nhập dữ liệu ngày: 10/03/2007
   C1=songay(a1,b1,1) =9 '1,2,3,4,5,6,7 là thứ tự các thứ trong tuần CN là 1
   Bạn xem lại

   Bạn thử xem hàm này có dùng dc ko nha!
   =INT((A2-WEEKDAY(A2)-A1+8)/7)
   Rất đơn giãn! Thử xem đúng ko?
   Mến
   ANH TUẤN

   Ah hình như không đúng thì phải các Bác thử lại xem. Nếu cùng năm thì đúng còn khác năm thì không.Mong Bác anhtuan1066 xem lại và sửa giúp

   Bạn yeudoi thử cho 1 VD cụ thể đi, trường hợp nào cho kết quả sai? Tôi đã thử cả ở những năm khác nhau nhưng chưa phát hiện trường hợp nào sai như bạn nói!
   Có nhiều cách đễ biết dc có bao nhiêu ngày CN giữa 2 thời điểm cho trước X, Y (Với X=<Y). Tất cả dựa trên nguyên tắc: "Trong 7 ngày liên tiếp có ít nhất 1 ngày CN"
   Vậy bây giờ ta xét các kết quả sau:
   1/- Xét phép chia: INT((A2-A2+1)/7) (Tạm gọi là kết quả (a))
   2/- Xét phần dư MOD(A2-A1+1,7) (Tạm gọi là kết quả (b))
   3/- Xét xem ngày CN đầu tiên là ngày thứ mấy so với móc thời gian X. Hoặc xét ngày CN cuối cùng cách bao nhiêu ngày so với móc thời gian Y (Tạm gọi đây là kết quả (c)).
   Kinh nghiệm của tôi cho thấy nên xét ngày CN cuối cùng so với móc thời gian Y thì sẽ dễ cho việc tính toán hơn (Đễ ý weekday(A2) có liên quan đến ngày CN cuối cùng. Weekday(A2) = bao nhiêu thì ngày CN cuối cùng cách Y bấy nhiêu)
   Cuối cùng ta có thể kết luận rằng: Nếu (c)=< (b) thì số ngày CN là (a)+1, ngược lại số ngày CN là (a)
   VD: từ X đến Y có 23 ngày. Vậy (a)=3 và (b)=2. Vậy nếu như ngày CN đầu tiên nằm lọt trong khoảng 2 ngày đầu hoặc ngày CN cuối cùng cách Y nhỏ hơn 2 (nghĩa là (c)=<2) thì từ X đến Y sẽ có (a)+1= 4 ngày CN. Ngược lại thì từ X đến Y có 3 ngày CN
   Cách suy luận là thế, còn tùy theo cách mỗi người tạo công thức như thế nào đó miễn sao có thể hiểu dc.
   Chẳng hạn tôi cũng có thể ra 1 công thức dài hơn nhưng tương đối dễ hiểu hơn như sau:
   =IF(MOD(A2-A1+1,7)>=WEEKDAY(A2),INT((A2-A1+1)/7)+1,INT((A2-A1+1)/7))
   Mong nghe thêm những ý kiến đóng góp khác!
   Mến
   ANH TUẤN

   Rất hay! Cám ơn anhtuan1066 nhiều!
   Theo tôi cũng không cần Int làm gì, chỉ cần
   =(A2-WEEKDAY(A2)-A1+8)/7 'số này luôn chia chẵn cho 7
   Mấy hôm nay cứ máy móc
   =($A2-(WEEKDAY($A2)-1))-($A1+MOD((8-WEEKDAY($A1)),7))+7
   CN đầu =$A1+MOD((8-WEEKDAY($A1)),7) 'sau ngày đầu
   CN cuối=$A2-(WEEKDAY($A2)-1) 'trước ngày cuối
   Hiệu số CN đầu – CN cuối =($A2-(WEEKDAY($A2)-1))-($A1+MOD((8-WEEKDAY($A1)),7))
   Lấy hiệu số chia cho 7 sau đó + 1 =>số CN
   =(($A2-(WEEKDAY($A2)-1))-($A1+MOD((8-WEEKDAY($A1)),7)))/7 +1
   Đơn giản là công thức tính CN cuối trước ngày cuối.

   Cần INT chứ bạn! Số (A2-WEEKDAY(A2)-A1+8) đâu có chia hết cho 7 trong mọi trường hợp đâu. VD nha! A1=15-5-2007… A2=15-6-2007… Vậy thì (A2-weekday(A2)-A1+8)=33 làm sao chia hết cho 7?
   mến
   ANH TUẤN

   Mình xin lỗi bạn anhtuan1066. Lúc tối gửi xong mình về thử lại bằng VBA thấy vẫn như vậy mà quên rằng đây là tính số ngày chủ nhật giữa hai ngày. Mình cứ tưởng số ngày giữa hai ngày trừ ngày chủ nhật. Thành thật xin lỗi các bạn.

   Tôi xin đính chính bài viết lần trước. Đúng ra nó phải như vầy: "Trong 7 ngày liên tiếp chỉ có duy nhất 1 ngày CN, bất kể ngày CN ấy nằm ở vị trí nào trong 7 ngày này"
   Xin lỗi!
   ANH TUẤN

   Mình suy diễn từ ý bạn anhtuan1066, lập thêm 1 hàm tính bao nhiêu ngày bất kỳ trong khoảng thời gian. Bạn có thể thay số 1 bold thành 2->7
   =IF(OR(WEEKDAY($A$2)=1,WEEKDAY($B$2)=1),INT(($B$2-$A$2)/7)+1,INT(($B$2-$A$2)/7))
   "Sai rồi" Điều chỉnh bài kế
   Lấy Int((ngày cuối -ngày đầu)/7), nếu ngày đầu hay ngày cuối là thứ 1 (…), dù thế nào thì hiệu số (ngày cuối -ngày đầu) nếu >7 đều có 1 ngày thoả dl là có ngày thứ i
   Từ đó vận dụng viết 1 code (đang học mà) nhờ các bạn test hộ.

   Cám ơn các bạn cho ý kiến.

   Có cách nào dựa trên công thức INT((A2-A1-weekday(A2)+8)/7) đễ làm ra 1 công thức khác có khả năng tính dc có bao nhiêu ngày bất kỳ giữa 2 thời điễm ko nhỉ?
   Đễ tính ngày CN thì:
   1/- Đầu tiên người ta xét xem ngày cuối cùng là ngày thứ mấy (bằng cách xét weekday (Y)), sau đó cộng thêm 1 số ngày nữa sao cho ngày cuối trở thành ngày THỨ BẢY (Satuday). Lấy tổng số ngày (Y-X+1) trừ đi weekday(Y) rồi cộng thêm 7 ta luôn luôn có được ngày cuối cùng là ngày THỨ BẢY (Y-X+1-weekday(Y)+7) hay (Y-X-weekday(Y)+8). Việc cộng thêm này không làm thay đổi số lượng ngày CHỦ NHẬT đang có giữa khoảng thời gian X, Y
   2/- Phân chia toàn bộ số ngày (Y-X-weekday(Y)+8) thành nhiều nhóm nhỏ, mỗi nhóm là 7 ngày, bắt đầu từ CHỦ NHẬT và kết thúc ở THỨ BẢY. Riêng nhóm đầu tiên bắt đầu từ ngày X đến THỨ BẢY. Giã sử ta đang có n+1 nhóm thì loại trừ nhóm đầu tiên ra, ta luôn luôn có n ngày CHỦ NHẬT, và tổng số ngày của n nhóm này luôn chia hết cho 7 (vì mỗi nhóm có 7 ngày).
   3/- Với nhóm đầu tiên (từ X đến THỨ BẢY) thì tổng số ngày của nó chính là phần dư trong phép chia (Y-X-weekday(Y)+8)/7. Nếu nhóm đầu tiên này bắt đầu bằng ngày CHỦ NHẬT thì mặc nhiên phần dư sẽ bằng 7 và ta có thêm 1 ngày CHỦ NHẬT nữa đã được cộng sẳn vào kết quả.
   Tôi cảm giác rằng đễ tính số ngày bất kỳ thì công thức này có thể sử dụng dc nhưng phải thay đỗi cái gì đó… Cái gì đó là cái gì nhỉ? Sao nghĩ hoài vẫn ko ra. Dạo này đầu óc bả đậu quá!

   1. Hàm đếm ngày CN bằng VBA

   Option Explicit
   Public Function DemCN(startDate As Date, EndDate As Date)
   Dim i, dem
   Dim date1 As Date
   i = EndDate - startDate
   For i = 0 To EndDate - startDate Step IIf((EndDate - startDate) >= 0, 1, -1)
       date1 = startDate + i
       If WorksheetFunction.Weekday(date1, 1) = 1 Then dem = dem + 1
   Next i
   DemCN = dem
   End Function

   2. Đếm ngày CN bằng hàm của Excel

   =IF(WEEKDAY(A1)=1,
   INT((A2-A1)/7)+INT((A2-A1-INT((A2-A1)/7)*7+WEEKDAY(A1,1))/8)+1,
   INT((A2-A1)/7)+INT((A2-A1-INT((A2-A1)/7)*7+WEEKDAY(A1,1))/8))

   Tổng hợp hàm đếm ngày CN!

   Làm thử 1 ví dụ với các công thức và Code của anh anhtuan1066, NVSON, Digita về việc tính bao nhiêu ngày CN trong khoảng thời gian. Tôi xin tổng hợp lại, mong các bạn nghiên cứu, giải nghĩa hộ và cho ý kiến. Trong đó công thức của anhtuan1066 là ngắn nhất.
   A3 ngày đầu
   B3 ngày cuối
   1/=(($B3-(WEEKDAY($B3)-1))-($A3+MOD((8-WEEKDAY($A3)),7)))/7 +1
   2/=INT(($B3-WEEKDAY($B3)-$A3+8)/7) anhtuan1066
   3/=IF(WEEKDAY($A3)=1,INT(($B3-$A3)/7)+INT(($B3-$A3-INT(($B3-$A3)/7)*7+WEEKDAY($A3,1))/8)+1,INT(($B3-$A3)/7)+INT(($B3-$A3-INT(($B3-$A3)/7)*7+WEEKDAY($A3,1))/8))
   NVSON
   4/=IF(OR(WEEKDAY($A3)>WEEKDAY($B3),WEEKDAY($A3)=1,WEEKDAY($B3)=1),INT(($B3-$A3)/7)+1,INT(($B3-$A3)/7))
   Dễ hiểu nhất
   VBA
   1/=songay($A3,$B3,1)

   Function SoNgay(ngaydau As String, ngaycuoi As String, Thu As Integer) As Integer
   Dim i As Integer
   Dim j As Integer
   Dim ThoiGian As Integer
   'Ngay kieu Eng hay Fren deu OK
   Fngay1 = DateSerial(Year(ngaydau), Month(ngaydau), Day(ngaydau))
   Fngay2 = DateSerial(Year(ngaycuoi), Month(ngaycuoi), Day(ngaycuoi))
   ThoiGian = Fngay2 – Fngay1
   'Thu=1,2,3,4,5,6,7 1 la CN
   If Weekday(Fngay1) > Weekday(Fngay2) Or Weekday(Fngay2) = Thu Or Weekday(Fngay1) = Thu Then
   SoNgay = Int(ThoiGian / 7) + 1
   Else
   SoNgay = Int(ThoiGian / 7)
   End If
   End Function

   2/=songaynghi($B3,$A3,1)

   Function SoNgayNghi(ngay1 As String, ngay2 As String, Thu As Integer) As Integer
   Dim i As Integer
   Dim j As Integer
   Dim ThoiGian As Integer
   Dim ngaycuoi As Date
   Dim ngaydau As Date
   Fngay1 = DateSerial(Year(ngay1), Month(ngay1), Day(ngay1))
   Fngay2 = DateSerial(Year(ngay2), Month(ngay2), Day(ngay2))
   
   If Fngay1 > Fngay2 Then
   ngaycuoi = Fngay1
   ngaydau = Fngay2
   Else
   ngaycuoi = Fngay2
   ngaydau = Fngay1
   End If
   
   'lay hieu so ngaycuoi-ngaydau
   'neu thu =0, tinh bao nhieu T7+CN
   If Thu > 8 Then
   MsgBox ("Ban nhap sai-nhap lai thu, thu < 8")
   Exit Function
   End If
   ThoiGian = ngaycuoi - ngaydau
   For i = ThoiGian To 0 Step -1
   '1 la CN, 2 la thu 2 ....,7 la thu 7
   'neu thu =0 tinh bao nhieu thu bay va cn
   If Thu = 0 And (Weekday(ngaycuoi - i) = 1 Or Weekday(ngaycuoi - i) = 7) Then
   SoNgayNghi = SoNgayNghi + 1
   End If
   If Weekday(ngaycuoi - i) = Thu Then
   SoNgayNghi = SoNgayNghi + 1
   End If
   Next i
   End Function

   3/=songaydigita($B3,$A3,1,$AA$2:$AA$2)
   Giả dụ ngày lễ là rỗng $AA$2:$AA$2
   Phần này sẽ có sai khi thứ ngày đầu <thứ ngày cuối hay ngày đầu =thứ ngày cuối =1

   Function SoNgayDiGiTa(EndDate As Date, startDate As Date, Sw As Integer, ngayle As Range) As Integer
   'Cú pháp =SoNgayDiGiTa(ngay cuoi, ngay dau, Sw, Vung co ngay le)
   Dim i As Integer
   Dim TimeLen As Integer
   
   If Format(startDate, "#,##0") = Format(EndDate, "#,##0") Or Sw > 11 Or Sw < -1 Then
   MsgBox ("Ngay Cuoi > Ngay Dau, Sw = tu -1 den 11, Vung liet ke ngay le")
   Exit Function
   End If
   TimeLen = DateDiff("d", startDate, EndDate)
   For i = TimeLen To 0 Step -1
   If Weekday(i, vbSunday) = Sw Then
   SoNgayDiGiTa = SoNgayDiGiTa + 1
   ElseIf Sw = 8 And (Weekday(EndDate - i) = 1 Or Weekday(EndDate - i) = 7) Then
   SoNgayDiGiTa = SoNgayDiGiTa + 1
   ElseIf Sw = 9 And WorksheetFunction.CountIf(ngayle, EndDate - i) > 0 Then
   SoNgayDiGiTa = SoNgayDiGiTa + 1
   ElseIf Sw = 10 And (WorksheetFunction.CountIf(ngayle, EndDate - i) > 0 Or (Weekday(EndDate - i) = 1 Or Weekday(EndDate - i) = 7)) Then
   SoNgayDiGiTa = SoNgayDiGiTa + 1
   ElseIf Sw = 11 And (WorksheetFunction.CountIf(ngayle, EndDate - i) = 0 And (Weekday(EndDate - i) <> 1 And Weekday(EndDate - i) <> 7)) Then
   SoNgayDiGiTa = SoNgayDiGiTa + 1
   End If
   Next i
   If Sw = -1 Then SoNgayDiGiTa = TimeLen
   If Sw = 0 Then SoNgayDiGiTa = TimeLen + 1
   End Function

   4/=demcn($A3,$B3) NVSON

   Public Function DemCN(startDate As Date, EndDate As Date)
   Dim i, dem
   Dim date1 As Date
   i = EndDate - startDate
   For i = 0 To EndDate - startDate Step IIf((EndDate - startDate) >= 0, 1, -1)
   date1 = startDate + i
   If WorksheetFunction.Weekday(date1, 1) = 1 Then dem = dem + 1
   Next i
   DemCN = dem
   End Function

   Trong các cách trên chỉ có cách của anh digita là xem lại, (so với 7 cách còn lại) cho đáp số sai
   Ví dụ sau với ngẫu nhiên khoảng 1000 biến.

   Bạn ThuNghi thử nghiên cứu xem, tôi đễ ý thấy rằng việc đếm số ngày CN và số ngày thường chỉ khác nhau có 1 điểm duy nhất thôi, đó là weekday(ngày CN) thì = 1. tức là số đầu tiên trong chu kỳ 1,2,… 7. Vậy bạn hãy nghĩ xem nếu ta muốn đếm số ngày thường, chẳng hạn là THỨ TƯ đi, thì có cách nào đó chuyễn đỗi weekday(thứ tư) này thành số 1 dc ko?. Ý tôi ko phải là thay đỗi hàm weekday, mà làm 1 công việc chuyễn đỗi thôi, chuyễn THỨ TƯ thành số 1, lúc này THỨ NĂM sẽ = 2… và THỨ BA = 7. Lúc đó ta sẽ xem nó như 1 ngày CN bình thường và dùng công thức đễ đếm như các trường hợp ở trên.
   Tôi cãm giác là thế mà nghĩ hoài vẩn ko ra. Bạn thử xem giãi pháp này có khả thi ko?

   Ah, tôi vừa nghĩ ra cách tính số ngày bất kỳ giữa 2 thời điễm cho trước. Tôi post file lên các bạn xem thử có thể rút gọn dc ko nha! Vì trong file, khi tính số ngày nào đó ko phải là CN thì tôi phải dùng quá nhiều cột phụ, Thấy ko hay cho lắm! Đã vậy tôi chỉ mới thử sơ qua thôi, chưa biết có sai xót gì nữa ko. Nhờ các bạn xem giúp. (Tôi cũng đang áp dụng cách mà tôi vừa nói ở trên)
   Mến
   ANH TUẤN

   Reply
2. hands says:

   31/01/2023 at 7:01 pm

   cảm ơn bác Anh Tuấn nhiều nhiều. em có 1 bài mong bác giúp dùm em

   chào chị THu Nghi! em thấy chị là 1 thành viên có nhiều bài viết khá hay. em là dân kế toán mới vào nghề và cũng là thành viên mới của diễn đàn có gì mong chị và các bạn giúp đỡ. em có một bài tính tổng số ngày chủ nhật giữa 2 khoảng thời gian mong chị giúp đỡ

   Bài toán này chỉ khó ở chổ xác định: Có bao nhiêu ngày CN giữa 2 thời điểm
   Làm thử, bạn xem thế nào nhé

   =INT((A2-A1-WEEKDAY(A2-6,2) +8)/7)
   dạ cho em hỏi tại sao trong công thức này lai +8 vậy ạh

   Công thức này phải khai triển thế này mới có thể hiểu được
   =INT(((A2 – WEEKDAY(A2-6,2)+7)–A1+1))/7)
   Với WEEKDAY(A2-6,2) chính bằng WEEKDAY(A2)
   Vậy viết lại công thức:
   =INT(((A2 – WEEKDAY(A2)+7)–A1+1))/7)
   Ta xét (A2 – WEEKDAY(A2)+7) —-> Một ngày trừ đi WEEKDAY của ngày ấy rồi cộng thếm 7 sẽ luôn luôn là ngày thứ 7 tới —> Và sự gia giảm này không hề làm thay đổi tổng số ngày CN (Ý nghĩa của sự gia giảm này là "dịch" ngày cuối cùng đến ngày thứ 7 tới gần nhất)
   Giả sử ta đặt NCGL = (A2 – WEEKDAY(A2)+7) tạm gọi là ngày cuối giả lập
   (A2 – WEEKDAY(A2)+7)–A1+1 = NCGL– A1+1 Chính là tổng số ngày
   Phần nguyên của phép chia NCGL– A1+1 với 7 chính là tổng số ngày CN —> Vậy thôi
   ———————
   Thuật toán có thể hiểu ngay (nằm ở chổ A2 – WEEKDAY(A2)+7 ấy), nhưng giải thích quả thật là khó… Hic…

   chào anh Tuấn
   Rất cám ơn Anh về đề công thức có thể đếm đươc số ngày của bất kỳ trong một khoảng thời gian.
   nhưng cách xác định ngày chủ nhật đầu tiên Tôi thấy anh viết: Ngày CHỦ NHẬT đầu tiên: = A1 – WEEKDAY(A1,2)+ 7.
   Tôi thử kiểm tra trong khoảng thời gian từ ngày 10/01/2007 đến ngày 27/01/2007 thì nó là ngày 13/01/2007 tương ứng đó là ngày thứ bẩy chứ không pải là ngày chủ nhật. Nếu tôi thay 7 => 8 thì là ngày 14/01/2007 thì đúng là ngày chủ nhật.
   Tôi đang phân vân ??

   Hôm nay thức khuya mới phát hiện bài này —> Chẳng hiểu bạn tính cách nào mà ra thứ bảy nữa
   Này nhé:
   – Ta có ngày 10/01/2007 tại cell A1
   – Suy ra WEEKDAY(A1,2) = 3 (chú ý có số 2 trong Weekday nhé)
   – Suy ra A1 – WEEKDAY(A1,2)+ 7 = 10/01/2007 – 3 + 7 —-> Chẳng bằng 14/01/2007 là gì?
   Sao bạn lại ra được kết quả 13/01/2007 cơ chứ?

   Code dưới đây sẽ liệt kê các ngày chủ nhật giữa hai khoảng ngày/tháng/năm
   Dim i As Date: Dim x$
   For i = Text1 To Text2
   If Weekday(i) = 1 Then x = x & ", " & i
   Next
   MsgBox "Cac ngay chu nhat la : " & x

   Một cách khác để bạn thêm tham khảo: Hàm mảng tự tạo

   File của bạn HYen17 thì hay quá rồi, nhưng mình không rành về VBP, có cách nào đơn giản, dễ hiểu hơn không -\/.

   Giả định bạn có 2 giá trị StartDate và EndDate, với EndDate > StartDate
   – Vậy, bằng cách nào đó, bạn tìm được 1 giá trị Start_1 >= StartDate lại vừa là ngày CN. Hãy gõ nó vào 1 cell.
   – Cell bên dưới sẽ bằng cell trên cộng thêm 7 (ngày CN kế tiếp)
   – Cứ thể kéo fill đến khi nào thấy vượt quá EndDate thì ngưng (ta có thể lồng thêm điều kiện để nó tự "ngưng")
   Quá dễ
   Gợi ý cho bạn giá trị Start_1 = StartDate – WEEKDAY(StartDate,2)+ 7

   Cám ơn bạn nhiều, 1 công thức đơn giản hiệu quả! }}}}}
   Riêng ngày thứ 2 chỉnh thành =INT((A2-A1-WEEKDAY(A2-0,2)+8)/7) exxcel mới hiểu

   thêm một cách mới cho excel 2010—>trở lên
   đếm bao nhiêu ngày chủ nhật

   =$B1-$A1-NETWORKDAYS.INTL($A1,$B1,11,0)+1

   với a1 là ngày bắt đầu
   b1 là ngày kết thúc
   thay đổi tham số từ 11–> 17 để tính các ngày còn lại–=0

   Mình làm theo mặc định tính số ngày chủ nhật trong 1 tháng như sau, không biết có đúng không? Xin tham khảo ý kiến. (Cái này dành cho Excel 2007 trở xuống)
   =INT(DAY("Ngày cuối"-WEAKDAY("Ngày cuối")+1)/7)+1
   Giải thích:
   1/ WEEKDAY("Ngày cuối") là lấy ra ngày cuối là thứ mấy?
   2/ "ngày cuối"-WEAKDAY("Ngày cuối")+1 sẽ là ngày của chủ nhật cuối cùng
   3/ INT(DAY("Ngày cuối"-WEAKDAY("Ngày cuối")+1)/7) sẽ là số ngày chủ nhật có trong 1 tháng nhưng chưa tính ngày chủ nhật cuối cùng nên ta phải +1 như công thức đã trình bày.
   4/ Nếu phát sinh lỗi chưa logic xin các bạn cho ý kiến.
   "Ngày cuối của tháng"=EOMONTH(TODAY();0)
   "Ngày đầu của tháng"=EOMONTH(TODAY();-1)+1

   Đối với Excel 2010 trở lên thì mọi việc quá đơn giản bằng cách dùng hàm
   =NETWORKDAYS.INTL("Ngày đầu";"Ngày cuối";11)

   Reply