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ỳ (1 người xem)

[vungoc]

Chào cả nhà !

Mình có thể dùng hàm hoặc code nào để tính được số ngày Chủ Nhật trong một quãng thời gian các bác nhỉ ?

Ví dụ:
Ô A1 Nhập dữ liệu ngày: 01/01/2007
Ô B1 Nhập dữ liệu ngày: 10/03/2007
Ô C1, Công thức hoặc code nào để tính được kết quả số ngày Chủ Nhật từ A1:B1 (Theo mình tính thủ công đếm theo trên lịch là 9 ngày Chủ Nhật cho quãng thời gian trên).

Mong sớm nhận được sự trợ giúp của cả nhà.

Chân thành cảm ơn !

 

[ThuNghi]

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

 

[anhtuan1066]

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

 

[yeudoi]

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

 

[anhtuan1066]

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

 

[ThuNghi]

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.

 

[anhtuan1066]

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

 

[yeudoi]

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.

 

[digita]

Mời bạn Vũ Ngọc tham khảo cái thread nầy nhé.

http://www.giaiphapexcel.com/forum/showthread.php?t=2476

 

[anhtuan1066]

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

 

[ThuNghi]

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.

 

[anhtuan1066]

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á!

 

[nvson]

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))

 

[ThuNghi]

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.

 

[anhtuan1066]

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

 

[anhtuan1066]

Tại sao chúng ta lại quên rằng hàm WEEKDAY còn có tham số nữa, là 1, 2 hoặc 3. Trước giờ cứ quen xài dạng WEEKDAY (X), mặc định ko ghi gì có nghĩa là tham số = 1, WEEKDAY(X) cũng = WEEKDAY(X,1). Tôi vừa phát hiện ra 1 công thức tổng quát khác. Nó có thể tính được số ngày bất kỳ giữa 2 móc thời gian:
Tính số ngày thứ hai: =INT((A2-A1-WEEKDAY(A2,2)+8)/7)
Tính số ngày thứ ba: =INT((A2-A1-WEEKDAY(A2-1,2)+8)/7)
Tính số ngày thứ tư: =INT((A2-A1-WEEKDAY(A2-2,2)+8)/7)
Tính số ngày thứ năm: =INT((A2-A1-WEEKDAY(A2-3,2)+8)/7)
Tính số ngày thứ sáu: =INT((A2-A1-WEEKDAY(A2-4,2)+8)/7)
Tính số ngày thứ bảy: =INT((A2-A1-WEEKDAY(A2-5,2)+8)/7)
Tính số ngày chủ nhật: =INT((A2-A1-WEEKDAY(A2-6,2)+8)/7)
Và cũng từ đây, ta có thể suy ra nhiều công thức khác, chẳng hạn tìm ngày CHỦ NHẬT đầu tiên, ngày CHỦ NHẬT cuối cùng:
Ngày CHỦ NHẬT đầu tiên: = A1 - WEEKDAY(A1,2)+ 7
Ngày CHỦ NHẬT cuối cùng: = A2 - WEEKDAY(A2) + 1
Rất cám ơn bạn VuNgoc đã đưa ra 1 đề tài khá thú vị
Mến!
ANH TUẤN

 

[speed_123456]

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 đỡ

 

[ndu96081631]

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

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é

 

[thuongnguyen89]

=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

 

[ndu96081631]

=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...